SSWUG Radio

Chuck Boyce (http://chuckboyce.blogspot.com/) has a daily session called "The Where Clause". He makes an overview of the interesting blog posts of the day and yesterday he mentioned mine :-)

Check it out: http://www.sswug.org/sswugradio/the_where_clause_02feb2006.mp3

Thanks Chuck.

Equijoin and search predicates in SQL Server 2005

In older versions of SQL Server a common optimization technique for equijoins with a criteria on one of the fields of the equijoin was to add the criteria for both tables.

eg.

SELECT p.myID FROM tblParent p
JOIN tblChild c ON p.myID = c.myID
WHERE c.myID > 3 AND p.myID > 3

Because we are talking about an equijoin one can conclude that the myID field should be > 3 for both tables if it was requested for one of the tables.

SQL Server 2005 however is a bit smarter than older versions and comes up with a correct query plan all by itself.

When executing the following query in SQL Server 2005 you will see in the query plan that SQL Server takes into account the equijoin with the correct criteria for both tables:

SELECT p.myID FROM tblParent p
JOIN tblChild c ON p.myID = c.myID
WHERE c.myID > 3

Notice how the the Seek Predicates for tblParent contains myID > 3 too.

Another little optimization that makes your life a little easier. The best part is that optimizations like this are for free, meaning no changes have to be made to your existing queries to benefit from this. There are a couple more optimizations like these, for example: statement level recompilation for stored procedures, caching of plans that use dynamic objects (like table variables), various tempdb optimizations, ...

As a side note do remember that the recommendations for tempdb in SQL Server 2000 are still valid for SQL Server 2005. For those of you that don't know them or have forgotten them:

• Avoid autogrowth
• Use as many files as there are CPU's (take into account the processor affinity setting)
• Equally size the files