Raja M (4/16/2014)
Interesting article, but I question the test harness.I think you should create a more realistic table that has a sizable number of columns. There should be a significant difference between a table-scan and an index-scan which then translates into more real life performance numbers.
You should include full query plans for all matters related to performance.
Thanks.
Raja,
If you read the blog linked into the article, you'll see that returning more columns from the table (when they're outside of the INDEX) will break the SEEK. That's why I provided that extended information, and there is a way to go back to a SEEK even in that case.
Your comments about having a wider table are true for testing in your particular case, but that approach doesn't lend itself well to writing articles, where it is important to keep the examples as simple as possible. It is why I recommended testing carefully when you try this approach. I believe I even pointed out that I need to do more of that before I apply this to the Production case I'm working on (still not yet done).
And as to query plans, I posted 3 in the article. Was there one missing that you wanted to see?
Thanks for the comments.
Dwain.C
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St