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.