18 November 2009 08:59
tonyrogerson
SQL 2008 SP1 CU5 is now out and fixes the OPTION(RECOMPILE) Parameter Embedding Optimisation problem - properly!
SQL Server 2008 SP1 Cumulative Update (CU5) link: http://support.microsoft.com/kb/975977/LN/
Details on how they have fully fixed the OPTION( RECOMPILE ) Parameter Embedding Optimisation : http://support.microsoft.com/kb/976603/
What is Parameter Embedding Optimisation, well its basically you can do this...
WHERE email = coalesce( @email, email )
AND name = coalesce( @name, name )
OPTION ( RECOMPILE )
Basically, if @email is NULL then the optimisers optimises out that clause in the query thus giving better access to index choices and negating the need for dynamic SQL.
A very cool feature that came in with RTM, was disabled in CU4 because of the bug and fixed and renabled in CU5.
Filed under: SQL Server, SQL Development