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.