SQL Server 2005 question...

[Jaxidian]

I swear I have read that SQL Server 2005 doesn't care how your WHERE clause is organized, that it will always optimize it. By this, I don't mean poorly written (bad LIKE statements and whatnot), I just mean a combination of a bunch of AND and OR statements will be reorganized in a way that SQL Server thinks is the optimal way. For instance, I could say:

SELECT FOO FROM BAR WHERE
(FOO IS NOT NULL AND FOO<>'') OR (ID > 3)

and it might get changed to be:

SELECT FOO FROM BAR WHERE
(FOO IS NOT NULL OR ID > 3) AND (FOO<>'' OR ID > 3)

so no matter how I organize that WHERE logic, SQL Server would always optimize it in the same way.

I just can't find anything that confirms this. Does anybody know of a credible site (preferably MSDN) that states this?

Thanks!
-Jax

 

[troytime]

i don't think it would reorganize the AND/OR conditions, because that would defeat the purpose of logic

(foo = 1 AND bar = 2) OR bax = 3 is different than
(foo = 1 OR bax = 3) AND bar = 2

maybe you're thinking that it reorganizes the join order?

 

[Jaxidian]

Originally posted by: troytime
i don't think it would reorganize the AND/OR conditions, because that would defeat the purpose of logic

(foo = 1 AND bar = 2) OR bax = 3 is different than
(foo = 1 OR bax = 3) AND bar = 2

maybe you're thinking that it reorganizes the join order?

Your translation is wrong. The logic rule is this, which you're not following:
(A & B) | C = (A | C) & (B | C)

For your example:
A="foo=1"
B="bar=2"
C="bax=3"

So......

(foo=1 AND bar=2) OR bax=3
is the same as
(foo=1 OR bax=3) AND (bar=2 OR bax=3)

 

[troytime]

wasn't meant to be a translation, just an example

i can't find any documentation about it