Query problem

CREATE TABLE [Table1] (
[abc] [int] ,
[xyz] [char] (10)
)

insert into abc (1,'z')
insert into abc (2,'y')
insert into abc (3,'z')
select * from table1 where abc in (3,2)
i want the output as follows
3 z
2 y
not

2 y
3 z
please help me out
You should use an ORDER BY:
SELECT * FROM table1 WHERE abc IN (3,2) ORDER BY abc DESC
|||it seems i haven't posted the question properly,
it's not the case of 3 and 2
it may be
SELECT * FROM table1 WHERE abc IN (3,2,8,4,1,7)
then it won't work
i hope i made more clear the Q
thanks

|||No, I'm sorry it's not clear. I really have no idea what you arelooking for. Maybe you can explain with more examples.
|||Do you just want to order it in descending rather than ascending order?

Query Problem

Hi, I have a problem with the WHERE IN statement
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> The IN clause will treat @.Id as a single value. If the list size is fixed,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > Hi, I have a problem with the WHERE IN statement
> >
> > The following statement works ok.
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN
> > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> >
> > But this statement throws a error : Conversion failed when converting
> > from a character string to uniqueidentifier.
> >
> >
> > declare @.Id varchar(4000)
> > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> >
> > Anyone have any Ideas?
> >
> > Thanks
> >
> > Toby
> >
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> >
> > The IN clause will treat @.Id as a single value. If the list size is fixed,
> > you can specify multiple parameters:
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> >
> > See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> > solutions. You also have additional XML options in SQL 2005.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Tobi" <toby.riley@.gmail.com> wrote in message
> > news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > > Hi, I have a problem with the WHERE IN statement
> > >
> > > The following statement works ok.
> > >
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN
> > > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> > >
> > > But this statement throws a error : Conversion failed when converting
> > > from a character string to uniqueidentifier.
> > >
> > >
> > > declare @.Id varchar(4000)
> > > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> > >
> > > Anyone have any Ideas?
> > >
> > > Thanks
> > >
> > > Toby
> > >
> >
> >
> >

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!
sql

query optimizer

Does anyone know if transact sql joins will work faster than ansii
standard: ex:
ANSI standard is this
Select field1, field2
>From table1, table2
Where table1ID=table2ID
Transact SQL is this
Select field1, field2
>From table1
Join table2 on table1ID=table2ID
Does the optimizer see these differently on a table with a lot of
joins?Both of those alternatives are standard ANSI code, both work find in
Transact-SQL, and SQL Server's optimizer will produce the same
execution plan for both.
Roy Harvey
Beacon Falls, CT
On Wed, 13 Jun 2007 12:23:04 -0700, Kristina <KristinaDBA@.gmail.com>
wrote:
>Does anyone know if transact sql joins will work faster than ansii
>standard: ex:
>ANSI standard is this
>Select field1, field2
>>From table1, table2
>Where table1ID=table2ID
>Transact SQL is this
>Select field1, field2
>>From table1
>Join table2 on table1ID=table2ID
>Does the optimizer see these differently on a table with a lot of
>joins?|||As Roy said, they will produce the same query plan. HOWEVER, I would advise
against using the 'old-style' joins if they are outer because they will not
be supported in future editions of SQL Server.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
> Does anyone know if transact sql joins will work faster than ansii
> standard: ex:
> ANSI standard is this
> Select field1, field2
>>From table1, table2
> Where table1ID=table2ID
> Transact SQL is this
> Select field1, field2
>>From table1
> Join table2 on table1ID=table2ID
> Does the optimizer see these differently on a table with a lot of
> joins?
>|||On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> As Roy said, they will produce the same query plan. HOWEVER, I would advise
> against using the 'old-style' joins if they are outer because they will not
> be supported in future editions of SQL Server.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
>
> > Does anyone know if transact sql joins will work faster than ansii
> > standard: ex:
> > ANSI standard is this
> > Select field1, field2
> >>From table1, table2
> > Where table1ID=table2ID
> > Transact SQL is this
> > Select field1, field2
> >>From table1
> > Join table2 on table1ID=table2ID
> > Does the optimizer see these differently on a table with a lot of
> > joins... Hide quoted text -
> - Show quoted text -
Good point, I read that =* isn't supported in 2005. is that correct?|||It is indeed correct. Try this in SQL 2k5:
select *
from sys.objects o, sys.indexes c
where o.object_id *= c.object_id
and you get this informative error:
Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
query without modification, please set the compatibility level for current
database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
non-ANSI join operators will not be supported even in backward-compatibility
modes.
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181863946.324696.186190@.o11g2000prd.googlegroups.com...
> On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> As Roy said, they will produce the same query plan. HOWEVER, I would
>> advise
>> against using the 'old-style' joins if they are outer because they will
>> not
>> be supported in future editions of SQL Server.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Kristina" <Kristina...@.gmail.com> wrote in message
>> news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
>>
>> > Does anyone know if transact sql joins will work faster than ansii
>> > standard: ex:
>> > ANSI standard is this
>> > Select field1, field2
>> >>From table1, table2
>> > Where table1ID=table2ID
>> > Transact SQL is this
>> > Select field1, field2
>> >>From table1
>> > Join table2 on table1ID=table2ID
>> > Does the optimizer see these differently on a table with a lot of
>> > joins... Hide quoted text -
>> - Show quoted text -
> Good point, I read that =* isn't supported in 2005. is that correct?
>|||On Jun 14, 9:39 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> It is indeed correct. Try this in SQL 2k5:
> select *
> from sys.objects o, sys.indexes c
> where o.object_id *= c.object_id
> and you get this informative error:
> Msg 4147, Level 15, State 1, Line 3
> The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
> query without modification, please set the compatibility level for current
> database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
> strongly recommended to rewrite the query using ANSI outer join operators
> (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
> non-ANSI join operators will not be supported even in backward-compatibility
> modes.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181863946.324696.186190@.o11g2000prd.googlegroups.com...
>
> > On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> As Roy said, they will produce the same query plan. HOWEVER, I would
> >> advise
> >> against using the 'old-style' joins if they are outer because they will
> >> not
> >> be supported in future editions of SQL Server.
> >> --
> >> TheSQLGuru
> >> President
> >> Indicium Resources, Inc.
> >> "Kristina" <Kristina...@.gmail.com> wrote in message
> >>news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
> >> > Does anyone know if transact sql joins will work faster than ansii
> >> > standard: ex:
> >> > ANSI standard is this
> >> > Select field1, field2
> >> >>From table1, table2
> >> > Where table1ID=table2ID
> >> > Transact SQL is this
> >> > Select field1, field2
> >> >>From table1
> >> > Join table2 on table1ID=table2ID
> >> > Does the optimizer see these differently on a table with a lot of
> >> > joins... Hide quoted text -
> >> - Show quoted text -
> > Good point, I read that =* isn't supported in 2005. is that correct... Hide quoted text -
> - Show quoted text -
Thanks for the help! I just left a job where I upgraded all the db's
to SQL 2005 and my current contract is in SQL 2000 and all the SQL is
ansii standard - I have never seen anything like it!!! Trying to work
through all the new issues.

query optimizer

Does anyone know if transact sql joins will work faster than ansii
standard: ex:
ANSI standard is this
Select field1, field2
>From table1, table2
Where table1ID=table2ID
Transact SQL is this
Select field1, field2
>From table1
Join table2 on table1ID=table2ID
Does the optimizer see these differently on a table with a lot of
joins?Both of those alternatives are standard ANSI code, both work find in
Transact-SQL, and SQL Server's optimizer will produce the same
execution plan for both.
Roy Harvey
Beacon Falls, CT
On Wed, 13 Jun 2007 12:23:04 -0700, Kristina <KristinaDBA@.gmail.com>
wrote:

>Does anyone know if transact sql joins will work faster than ansii
>standard: ex:
>ANSI standard is this
>Select field1, field2
>Where table1ID=table2ID
>Transact SQL is this
>Select field1, field2
>Join table2 on table1ID=table2ID
>Does the optimizer see these differently on a table with a lot of
>joins?|||As Roy said, they will produce the same query plan. HOWEVER, I would advise
against using the 'old-style' joins if they are outer because they will not
be supported in future editions of SQL Server.
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
> Does anyone know if transact sql joins will work faster than ansii
> standard: ex:
> ANSI standard is this
> Select field1, field2
> Where table1ID=table2ID
> Transact SQL is this
> Select field1, field2
> Join table2 on table1ID=table2ID
> Does the optimizer see these differently on a table with a lot of
> joins?
>|||On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> As Roy said, they will produce the same query plan. HOWEVER, I would advi
se
> against using the 'old-style' joins if they are outer because they will no
t
> be supported in future editions of SQL Server.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181762584.269445.155360@.q19g2000prn.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
Good point, I read that =* isn't supported in 2005. is that correct?|||It is indeed correct. Try this in SQL 2k5:
select *
from sys.objects o, sys.indexes c
where o.object_id *= c.object_id
and you get this informative error:
Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
query without modification, please set the compatibility level for current
database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
non-ANSI join operators will not be supported even in backward-compatibility
modes.
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181863946.324696.186190@.o11g2000prd.googlegroups.com...
> On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Good point, I read that =* isn't supported in 2005. is that correct?
>|||On Jun 14, 9:39 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> It is indeed correct. Try this in SQL 2k5:
> select *
> from sys.objects o, sys.indexes c
> where o.object_id *= c.object_id
> and you get this informative error:
> Msg 4147, Level 15, State 1, Line 3
> The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
> query without modification, please set the compatibility level for current
> database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
> strongly recommended to rewrite the query using ANSI outer join operators
> (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
> non-ANSI join operators will not be supported even in backward-compatibili
ty
> modes.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181863946.324696.186190@.o11g2000prd.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks for the help! I just left a job where I upgraded all the db's
to SQL 2005 and my current contract is in SQL 2000 and all the SQL is
ansii standard - I have never seen anything like it!!! Trying to work
through all the new issues.

query optimizer

Does anyone know if transact sql joins will work faster than ansii
standard: ex:
ANSI standard is this
Select field1, field2
>From table1, table2
Where table1ID=table2ID
Transact SQL is this
Select field1, field2
>From table1
Join table2 on table1ID=table2ID
Does the optimizer see these differently on a table with a lot of
joins?
Both of those alternatives are standard ANSI code, both work find in
Transact-SQL, and SQL Server's optimizer will produce the same
execution plan for both.
Roy Harvey
Beacon Falls, CT
On Wed, 13 Jun 2007 12:23:04 -0700, Kristina <KristinaDBA@.gmail.com>
wrote:

>Does anyone know if transact sql joins will work faster than ansii
>standard: ex:
>ANSI standard is this
>Select field1, field2
>Where table1ID=table2ID
>Transact SQL is this
>Select field1, field2
>Join table2 on table1ID=table2ID
>Does the optimizer see these differently on a table with a lot of
>joins?
|||As Roy said, they will produce the same query plan. HOWEVER, I would advise
against using the 'old-style' joins if they are outer because they will not
be supported in future editions of SQL Server.
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181762584.269445.155360@.q19g2000prn.googlegr oups.com...
> Does anyone know if transact sql joins will work faster than ansii
> standard: ex:
> ANSI standard is this
> Select field1, field2
> Where table1ID=table2ID
> Transact SQL is this
> Select field1, field2
> Join table2 on table1ID=table2ID
> Does the optimizer see these differently on a table with a lot of
> joins?
>
|||On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> As Roy said, they will produce the same query plan. HOWEVER, I would advise
> against using the 'old-style' joins if they are outer because they will not
> be supported in future editions of SQL Server.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181762584.269445.155360@.q19g2000prn.googlegr oups.com...
>
>
>
> - Show quoted text -
Good point, I read that =* isn't supported in 2005. is that correct?
|||It is indeed correct. Try this in SQL 2k5:
select *
from sys.objects o, sys.indexes c
where o.object_id *= c.object_id
and you get this informative error:
Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
query without modification, please set the compatibility level for current
database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
non-ANSI join operators will not be supported even in backward-compatibility
modes.
TheSQLGuru
President
Indicium Resources, Inc.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1181863946.324696.186190@.o11g2000prd.googlegr oups.com...
> On Jun 14, 8:26 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Good point, I read that =* isn't supported in 2005. is that correct?
>
|||On Jun 14, 9:39 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> It is indeed correct. Try this in SQL 2k5:
> select *
> from sys.objects o, sys.indexes c
> where o.object_id *= c.object_id
> and you get this informative error:
> Msg 4147, Level 15, State 1, Line 3
> The query uses non-ANSI outer join operators ("*=" or "=*"). To run this
> query without modification, please set the compatibility level for current
> database to 80 or lower, using stored procedure sp_dbcmptlevel. It is
> strongly recommended to rewrite the query using ANSI outer join operators
> (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server,
> non-ANSI join operators will not be supported even in backward-compatibility
> modes.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1181863946.324696.186190@.o11g2000prd.googlegr oups.com...
>
>
>
>
>
>
> - Show quoted text -
Thanks for the help! I just left a job where I upgraded all the db's
to SQL 2005 and my current contract is in SQL 2000 and all the SQL is
ansii standard - I have never seen anything like it!!! Trying to work
through all the new issues.

Query Optimization

IS there any way to rewrite this Query in optimized way?

SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))

Thanks...jDon't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.

SELECT t1.EmpId
from dbo.Table1 t1
where t1.dtmStatusDate =
(select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)

also try

SELECT t1.EmpId
from dbo.Table1 t1
where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)

query on procedure

Hi,

Please see the below procedure.

create procedure a1
as
begin

create table #table1
{
empid int;
empname varchar
}
insert into #table1 select empid,empname from employee where
empcode='50'

select e.* from employee e, #table1 as t1 where e.empid=t1.empid and
e.empname=t1.empname; /* query1 */

end

In location query1,empid , empname in #table1 substitutes all the
values at the time, i need to substitute each value individually in
location query1.

Is there any way to do this?meendar wrote:

Quote:

Originally Posted by

create procedure a1

I trust your production code will have meaningful procedure names.

Quote:

Originally Posted by

create table #table1
{
empid int;
empname varchar
}

Should be

empid int,
empname varchar(30) -- or whatever

Quote:

Originally Posted by

insert into #table1 select empid,empname from employee where
empcode='50'
>
select e.* from employee e, #table1 as t1 where e.empid=t1.empid and
e.empname=t1.empname; /* query1 */

Why are you doing this, instead of simply

select * -- you should really have an explicit list of fields
from employee
where empcode = '50'

Does the 'employee' table really have both empcode and empid? If
so, then are they both enforced as unique?

Quote:

Originally Posted by

In location query1,empid , empname in #table1 substitutes all the
values at the time, i need to substitute each value individually in
location query1.
>
Is there any way to do this?

I don't understand what you mean. Please provide an example of what
it does now, and of what you want it to do instead.