About query performance

1 Sep 2006 8:54 AM

Vycka

Hi,

Lets say I have two queries:

Query A:
Select *
From Table1 t1, Table2 t2
Where t1.ID = t2.ID and t1.Field = '123'

Query B:
Select *
From Table1 t1 Join Table2 t2 On t1.ID = t2.ID
Where t1.Field = '123'

Does SQL Server optimize query A (to query B) while running it? Is there
will be a difference in performance if I am using query A or query B?
If the server has some inner optimizations that makes no difference between
query A and query B, is there a difference between SQL server 2000 and SQL
server 2005?

Thanks for the answer

Vycka

1 Sep 2006 9:53 AM

Tibor Karaszi

No difference. Both are semantically the same thing and the optimizer know that. This has been true
at least since 7.0.

The modern join syntax was introduced in 6.5, and I don't remember whether the optimizer treated old
and new join syntax the same in *all cases* in 6.5 (I do recall some bugs with new join syntax in
6.5, which is why I'm a bit doubtful on 6.5).

If it was an outer join, however, the queries might have different semantics....

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Vycka" <vyciu***@one.lt> wrote in message news:OYlJwQazGHA.4920@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> Lets say I have two queries:
>
> Query A:
> Select *
> From Table1 t1, Table2 t2
> Where t1.ID = t2.ID and t1.Field = '123'
>
> Query B:
> Select *
> From Table1 t1 Join Table2 t2 On t1.ID = t2.ID
> Where t1.Field = '123'
>
> Does SQL Server optimize query A (to query B) while running it? Is there will be a difference in
> performance if I am using query A or query B?
> If the server has some inner optimizations that makes no difference between query A and query B,
> is there a difference between SQL server 2000 and SQL server 2005?
>
> Thanks for the answer
>
> Vycka
>