Inside Microsoft® SQL Server™ 2005: T-SQL Querying

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

Thus far in the chapter, I mainly focused 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. There are still many performance improvements to gain merely from the changing way you write your ...