April 2, 2003 at 12:48 pm
I was looking at the Kevin Kline's "Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server Part 2" on http://www.sqlservercentral.com/articles/quest/
On page 10 of the PDF, Kevin states that using this index: CREATE NONCLUSTERED INDEX ndx_foo ON foo(a, b, c, d)
with this query: Select * from foo WHERE b = @b-2 AND a = @a [/b]
he asserts, "Again, the index cannot be used, despite both columns being indexed, because the WHERE clause does not analyze the leftmost column first." [/b]
This is utter BS, is it not? The query will be optimized to use the available index by (essentially) reordering the WHERE clause.
April 2, 2003 at 1:23 pm
Previous to version 2000, what he says is absolutley correct. And I have found many instances in 2000 where it doesn't do the optimization that I would have expected, so even if 2000 allows for this, I wouldn't count on it too much.... after all, why make the optimizer work any harder than it has to, and I would be ashamed to perform bad practices simply because I could.....
So, while I can see where your coming from, and his statement is not 100% accurate, if people follow his suggestions, I would be happy.....
April 2, 2003 at 3:53 pm
I do not have the reference book with me today, but I'd put money that SQL Server 7 will also reorder the arguments of a where clause during the compile to take advantage of an appropriate index.
Anyone else have a BOL or other reference?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy