Jeff Moden (10/17/2012)
Just my 2 cents on indexing. Be careful when using only indexing to "tune" a query especially on OLTP tables. I'm not suggesting that you should avoid such a thing but if you can make a query run much faster by code rather than by index, you've not only decreased index maintenance time a bit, but you've also made INSERTs run a little faster (sometimes, a lot faster... I've seen some indexes actually cause timeouts because of extent splitting) and you've made the code more bullet proof for the future. Another advantage of increasing performance by code instead of by index is space savings on disk and backup/restore times.There are times where indexing is absolutely necessary and there are times where you really should bite the bullet and write some alternative code. If the code is using DISTINCT, really consider the latter.
Sounds like the techie explanation of my second signature line! 😛
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