Richard Campbell delivered an "as always" great session about SQL Query optimization, this year his special flavor of tools extend to differences in SQL 2005 and where very enlightening.
update: Here's the code.
One thing he covers has always been the fabulous Rozenshtein method of doing Crosstabs (Pivot tables) While this is not really necessary since we have the Case Statement; it is quite useful when you need to create something that will run in virtually any database that supports SQL statements or when you need to create dynamic SQL and for some reason can't use a Stored Procedure.
He noted the still persistent problem of crosstabs in SQL 2005 with the new Pivot Command. While this does help, it still forces you to hard code the column names or build the SQL Dynamically (which will bypass the compiler and optimizer). If you want to know the real reason for this, it's because the low-level plumbing is extremely difficult to achieve in a performant way and will most likely be included in a future release when they can pound the algorithms down to improve performance.
Another noted example is to get rid of cursors by using temp tables (and Table Variables I might add).
Also covered was the new Ranking commands in SQL 2005, and how to simulate them (with small differences) in SQL 2000.
Overall a most enjoyable session, Thanks Richard.
Copyright © 2003-2004 H. Steele Price, IV -
All opinions are my own, not necessarily those of my employer, your mother, or any government agency.