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 logic

Hi there

Could you help me with rewritting below query in ANSI format-- for sql server 2005

SELECT
VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,
Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt
FROM
VSL, Co, VslCo, VslTy
WHERE
VSL.VSLNM = @.VslNm and
Vsl.VslTyCd = VslTy.VslTyCd and
VSL.UVN *= VslCo.UVN and
Co.CoNo =* (Select VslCo1.MainHdgOwrCoNo
from VSL VSL1,VslCo VslCo1, VslTy VslTy1
where VSL1.UVN = VSL.UVN and
Vsl1.VslTyCd = VslTy1.VslTyCd and
VslCo1.UVN = VSL1.UVN)

Regards

Rahul

The general form would be:

SELECT t1.Col1, t2.Col1

FROM Table1 t1

INNER JOIN Table2 t2

ON t1.ID = t2.ID

WHERE t1.Col3 = @.MyParameter

AND t1.Col4 = (SELECT t3.Col1

FROM MyTable3 t3

INNER JOIN MyTable4 t4

ON t3.ID = t4.ID)

See SQL Server 2005 Books Online topic: ‘FROM (Transact-SQL)’ for more details.

|||*= means "LEFT JOIN" and =* means "RIGHT JOIN" (I think)

So what you're after is:

SELECT
VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,
Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt
FROM
VSL
JOIN

VslTy
on Vsl.VslTyCd = VslTy.VslTyCd
RIGHT JOIN

Co

on Co.CoNo = (Select VslCo1.MainHdgOwrCoNo
from
VSL VSL1
JOIN
VslCo VslCo1
on VslCo1.UVN = VSL1.UVN
JOIN VslTy VslTy1
on Vsl1.VslTyCd = VslTy1.VslTyCd
where VSL1.UVN = VSL.UVN
)

LEFT JOIN

VslCo
on VSL.UVN = VslCo.UVN
WHERE
VSL.VSLNM = @.VslNm

But because you have RIGHT JOIN and LEFT JOIN used together, you will want to confirm that it's doing the right thing. Best is to eliminate RIGHT JOINs, in favour of LEFT, because it's so much clearer for all involved.

And it's been so long since I've used =* and *=... I guess I could have them the wrong way around.|||

well *= means left outer join

But above trick didnt work, It is really making me scratching my head.

|||

SELECT

VSL.VSLNM

, VSL.UVN

, VSL.UnqCoNo

, Co.CoRootNm

, Vsl.VslSt

, VslTy.VslTyNm

, VslSt

, Vsl.FlagCd

, Vsl.YrBld

, Vsl.YrBld

, Vsl.Grtge

, Vsl.DdWtTge

, Vsl.FmaDdWt

FROM

VSL

JOIN

VslTy

ON

Vsl.VslTyCd = VslTy.VslTyCd

LEFT JOIN

VslCo

ON

VSL.UVN = VslCo.UVN

RIGHT JOIN

Co

ON

CoNo = (Select

VslCo1.MainHdgOwrCoNo

FROM

VSL VSL1

,VslCo VslCo1

, VslTy VslTy1

WHERE

VSL1.UVN = VSL.UVN

AND

Vsl1.VslTyCd = VslTy1.VslTyCd

AND

VslCo1.UVN = VSL1.UVN

)

WHERE

VSL.VSLNM = @.VslNm

|||Ok, try swapping where I've put 'LEFT' and 'RIGHT' - my definition may be the wrong way around. I guess =* could be LEFT... it's been too long.

Rob|||

Hi there

Many thanx to you all, with ur input I am able to crack it.

Right query is

SELECT

VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,

Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt

FROM

VSL Join VSLTy

on vsl.vsltycd=vslty.vsltycd

left join vslco

on vsl.uvn=vslco.uvn

left join co

on

co.cono=(Select VslCo1.MainHdgOwrCoNo

from VSL VSL1,VslCo VslCo1, VslTy VslTy1

where VSL1.UVN = VSL.UVN and

Vsl1.VslTyCd = VslTy1.VslTyCd and

VslCo1.UVN = VSL1.UVN)

where

VSL.VSLNM = @.VslNm