Post reply

Index Select and Query optimization

  • April 2, 2003 at 12:48 pm

    #105483

    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

    #452905

    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

    #452927

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply