Inside Microsoft® SQL Server® 2008: T-SQL Querying

An Examination of Set-Based vs. Iterative/Procedural Approaches and a Tuning Exercise

Thus far in the chapter, I focused mainly on index tuning for given queries. However, in large part, query tuning involves query revisions. That is, with different queries or different T-SQL code you can sometimes get substantially different plans, with widely varying costs and run times. In a perfect world, the ideal optimizer would always figure out exactly what you are trying to achieve, and for any form of query or T-SQL code that attempts to achieve the same thing, you would get the same plan—and only the best plan, of course. But alas, we’re not there yet. You still have many performance improvements to gain merely from changing the way you write your code. ...