Difference between the querries.

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

Quote:

Originally Posted by getmeidea

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

I am not sure but one uses a cross join, the best thing for you is to look up joins, also it is mainly to do with speed issues. In some cases it may actually be nessecary to use a inner join that a cross join can't do or doesn't allow.|||

Quote:

Originally Posted by getmeidea

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

this kind of qry may easy for join one or two tables with 1 or 2 fields, but if u go beyond that
you have to write many condition with combination in where clause
for join itself. some more conditions for selecting records after joining...

Difference between Enterprise Manager and Query Analyzer

Hi All,

What are the pros and cons between using Enterprise Manager or Query
Analyzer for my queries.

Currently I use Enterprise Manager because I prefer the interface and
only use Query Analayzer when queries time out in Enterprise Manager,
but I'm sure there must be more to it.

Regards,
Ciarn(chudson007@.hotmail.com) writes:

Quote:

Originally Posted by

What are the pros and cons between using Enterprise Manager or Query
Analyzer for my queries.
>
Currently I use Enterprise Manager because I prefer the interface and
only use Query Analayzer when queries time out in Enterprise Manager,
but I'm sure there must be more to it.

Query Analyzer is a tool that I use daily, and it's always open on my
desktop. Enterprise Manager I only start if I need to work with Agent jobs.

As far as I know EM does not even have a query window. Probably you are
using the Query Designer, which may look nice, but which may add junk to
your queries. And there is a whole lot of things you do in it. Only in a
query window in Query Analyzer (or a command-line tool like OSQL), you can
use all T-SQL statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Difference Between Comma Operator and Crossjoin?

I have two queries that appear to return the same result. Can someone explain how combining two dimensions with the comma operator differs from combining the same two dimensions using the crossjoin operator?

For example, two dimensions, account and sales. When I run this query with the cross join operator

select { [Measures].[Sales Count] } on columns,

{ ([Account].[Name].Allmembers *

[Date].[Month Val].[5] : [Date].[Month Val].

) } on rows

from warehouse

it returns the same results as this query when using the comma operator to create the set:

select { [Measures].[Sales Count] } on columns,

{ ([Account].[Name].Allmembers ,

[Date].[Month Val].[5] : [Date].[Month Val].

) } on rows

As crossjoin states, in example 1 above, every account is combined with every month value. When using the comma operator, I see the exact same results. Are these functionally equivalent? Any reason to use one method over the other?

Thanks....

AS2005 can be very forgiving - perhaps too forgiving - with MDX syntax...

First of all, despite appearances, there is no comma operator: the comma is used as a delimiter within a set and a tuple definition. Crossjoin you're ok with. What you're doing in your queries is producing a set of tuples containing every month crossjoined with every account and for the sake of clarity this is best written as an explicit crossjoin, either using the asterisk operator or the crossjoin function, something like this:

select { [Measures].[Sales Count] } on columns,
[Account].[Name].Allmembers * {[Date].[Month Val].[5] : [Date].[Month Val].}
on rows
from warehouse

In your first query you're putting the above crossjoin operation inside a set of parentheses (), which denote a tuple - and which AS is ignoring because you're producing a set of tuples anyway. In the second query because you've put a set definition rather than a member as the second item in your tuple definition AS must be assuming that you want to do a crossjoin and is again returning the set of tuples you're after, but this behaviour is new to me and from a maintenance point of view should probably be avoided.

Chris

|||

Hi Chris,

I'm wondering whether the comma syntax in the 2nd example (which I also recall being mentioned in Teo Lachev's As 2005 book) is technically a subcube expression, as defined for the SCOPE script statement or a Cell Calculation:

http://msdn2.microsoft.com/en-us/library/ms145989.aspx

>>

Subcube_Expression ::= (Auxiliary_Subcube [, Auxiliary_Subcube,...n])

Auxiliary_Subcube ::=
Limited_Set
| Root([dimension_name])
| Leaves([dimension_name])

>>

Mosha had mentioned in his blog that a subcube could get converted to a set, under certain circumstances (though unsupported):

http://sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

>>

How to work with Dimension Leaves

While Analysis Services may convert subcube returned by Leaves function to set (by choosing attribute hierarchies corresponding to the attributes), it is not supported usage.

>>

|||

Hmmm, possibly... In which case, if Mosha says it's unsupported, it's definitely to be avoided.

Chris

|||Agreed...crossjoin works fine. Thanks for the feedback.

Difference b/w SPs & Functions

1. cannot hv tran in functions
2. cannot include SPs in select queries or in from clause whereas functions
can be included. (exception using OPENQUERY for including SP in from clause)
What other differences?Hi Rakesh
Difference between Stored Procedure and UDF:
Returning Parameters:
UDF Should return a parameter
Stored Procedure need not return any value
UDF Returns only one parameter
Stored Procedures can return more than one parameters
UDF can return a table
Stored Procedure returns a resultset
Usage
UDFs can be used a inline functions and can also be executed explicitly
eg: inline function: SELECT getdate()
Stored Procedures cannot be used as inline functions
Hope this helps you.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rakesh" wrote:

> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas function
s
> can be included. (exception using OPENQUERY for including SP in from claus
e)
>
> What other differences?|||You cannot use INSERT/UPDATE/DELETE on base tables.
You cannot Use NonDeterministic functions inside UDF
A scalar valued user defined function can be used Anywhere a value is
expected, as in WHERE clause, check contraint etc.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||Rakesh
Visit at http://www.sql-server-performance.com/
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||"Chandra" wrote in message

> Difference between Stored Procedure and UDF:
> Returning Parameters:
> UDF Should return a parameter
> Stored Procedure need not return any value
Stored Procedures RETURN a value, even if you dont explicitly specify (0 by
default)

> UDF Returns only one parameter
> Stored Procedures can return more than one parameters
Stored Procedure can RETURN one one value and that must be integer.
You can use Output Parameters to get other datatypes back from the SP

> UDF can return a table
> Stored Procedure returns a resultset
Stored Procs can return multiple recordsets, UDF can return only one (table
valued udfs)

> UDFs can be used a inline functions and can also be executed explicitly
> eg: inline function: SELECT getdate()
What you mean by inline?

> Stored Procedures cannot be used as inline functions
EXEC yourvar= ProcName <ParamList>
Just plain Nitpicking :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping b
y
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:[vbcol=seagreen]
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in
a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impac
t
> than trying to take seconds (or even minutes) off of long running procedur
es
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> > Hi everyone,
> >
> > We are running a SQL Server 2000 instance which is getting hit with
> > hundreds of queries a minute. The vast majority of these are very
> > short-lived, low overhead queries. Some of them are highly resouce
> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> > utilization has climbed way up and I can't begin to figure out what is
> > causing it... the high volume of quick queries, or the low volume of
> > slow queries.
> >
> > I'm fairly skilled at SQL Server performance tuning, but this has got
> > me stumped. How can I isolate the category of queries are causing all
> > of the activity? I've tried using profiler, but it displays each of the
> > queries individually ... there's no way to group activity together by
> > meaningful categories (that I'm aware of).
> >
> > Can anyone point me in the right direction?
> >
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping by
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>> "Rich" <rich@.adgooroo.com> wrote in message
>> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
>> > Hi everyone,
>> >
>> > We are running a SQL Server 2000 instance which is getting hit with
>> > hundreds of queries a minute. The vast majority of these are very
>> > short-lived, low overhead queries. Some of them are highly resouce
>> > intensive (< 1%). My problem is that lately, our SQL Server CPU
>> > utilization has climbed way up and I can't begin to figure out what is
>> > causing it... the high volume of quick queries, or the low volume of
>> > slow queries.
>> >
>> > I'm fairly skilled at SQL Server performance tuning, but this has got
>> > me stumped. How can I isolate the category of queries are causing all
>> > of the activity? I've tried using profiler, but it displays each of the
>> > queries individually ... there's no way to group activity together by
>> > meaningful categories (that I'm aware of).
>> >
>> > Can anyone point me in the right direction?
>> >
>> The basic technique here is to use profiler or a server trace to gather
>> execution statistics for individual queries over a window of time. Then
>> load the results into a table and analyze them. For instance, grouping
>> by
>> query text (or truncated or scrubbed text) and then summing the IO and
>> CPU
>> statistics. This will isolate the queries driving the CPU use.
>> David
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impact
> than trying to take seconds (or even minutes) off of long running procedures
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> > Hi David,
> >
> > Great suggestion! I didn't know you could export this information from
> > Profiler into another format.
> >
> > One more question. Can you suggest which performance monitors I should
> > select in profiler to get just the query CPU and IO information for
> > completed queries? Or better yet, do you know of any online tutorials
> > with this information?
> >
> > Thanks!
> > -Rich
> >
> >
> > David Browne wrote:
> >> "Rich" <rich@.adgooroo.com> wrote in message
> >> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> >> > Hi everyone,
> >> >
> >> > We are running a SQL Server 2000 instance which is getting hit with
> >> > hundreds of queries a minute. The vast majority of these are very
> >> > short-lived, low overhead queries. Some of them are highly resouce
> >> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> >> > utilization has climbed way up and I can't begin to figure out what is
> >> > causing it... the high volume of quick queries, or the low volume of
> >> > slow queries.
> >> >
> >> > I'm fairly skilled at SQL Server performance tuning, but this has got
> >> > me stumped. How can I isolate the category of queries are causing all
> >> > of the activity? I've tried using profiler, but it displays each of the
> >> > queries individually ... there's no way to group activity together by
> >> > meaningful categories (that I'm aware of).
> >> >
> >> > Can anyone point me in the right direction?
> >> >
> >>
> >> The basic technique here is to use profiler or a server trace to gather
> >> execution statistics for individual queries over a window of time. Then
> >> load the results into a table and analyze them. For instance, grouping
> >> by
> >> query text (or truncated or scrubbed text) and then summing the IO and
> >> CPU
> >> statistics. This will isolate the queries driving the CPU use.
> >>
> >> David
> >

Development tool for first time developer?

I am about to start developing a web app using MS SQL Server and was wondering whether there is any tool that I can use to test queries towards a remote server?

Something like a command line or an easy way I can write queries and run them straight away through the server and check results? It is my first time developing in SQL so I need something that allows me to play around with queries in the server and check results to quickly get a hang os MS SQL Server syntax, stored procedures and such.

Best Regards

Alethenorio

From the nature of your question, I assume that you are using SQL Server Express.

Yes, you can download the SQL Server Management Studio Express client tools for working with SQL Server Express.

SSMSE and other resources are available from these links:

SQL Server 2005 Express Edition (Advanced/SSMS/BI Toolkit)
http://tinyurl.com/yelwr9 (SSMSE)

SQL Server 2005 SSMS Express Tutorial
http://tinyurl.com/nqsca

SQL Server 2005 Express Books Online Express Edition
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F

SQL Server 2005 Express Video Learning
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||

Thanks Arnie.

To be honest I am doing a project for a company and I have not yet got access to the web hotel but if express is the lower version of microsoft's SQl server I believe it is not the version they run here. They probably run the full capable version (Whatever that is).

Would you suggest the same tool for such or is there another tool better fit to handle higher versions of SQl server?

Regards

Alex

|||

You may wish to consider getting SQL Server Express for your own computer, and the client tools, for your learning process.

The version of SQL Server Management Studio (SSMSE) that works with SQL Express is very similar to the version provided with the 'paid' Editions.

Good luck as you get up to speed. I think you will enjoy learning about SQL Server.

Determining Relevance with Least Amount of Queries

Suppose I have a table of record IDs and known record keywords like
this...
id|keywords
=================================
134|-PRNTR-FL-PRNT-GRDLN-
167|-PRNT-GRDLN-
198|-GRDLN-
009|-PRNT-
451|-XCL-
893|-BLB-
800|-JST-BLX-
(Note vowels removed as a crude form of compression for this example to
be legible.)
...and someone submits a search form with the keyword values...
XCL PRNT GRDLN
...and I want to return a relevance result like...
relevance|id|keywords
=================================
2|134|-PRNTR-FL-PRNT-GRDLN-
2|167|-PRNT-GRDLN-
1|198|-GRDLN-
1|009|-PRNT-
1|451|-XCL-
...where ids 134 and 167 had 2 found occurrences, the others except 893
and 800 had 1 occurrence, and anything else that didn't match is not
returned. The highest number of occurrences are sorted to the top.
How does one achieve this in ANSI-92 SQL in the least amount of
queries?*untested*
select t.*, relevance
from your_table t
join(
select id, count(*) relevance
from your_table t
join (
select '%-XCL-%' token
union all
select '%- PRNT-%'
union all
select '%-GRDLN-%'
) tokens
on t.keywords like token
) r
on t.id = r.id
BTW, your table seems to violate 1NF