Database Programming: One More Point On Recompilations and Temp Tables

Scott Whigham left a very generous comment on my last post on recompilations and temp tables. Within that comment he made an excellent observation that I don't want you to miss:

One thing you might mention with respect to using temp tables and/or permanent table creation in your stored procs is to be careful to include such DDL at the beginning of your proc, not interleaved in the code logic. Do a search in that article for "Recompilations due to mixing DDL and DML"...

(Scott is referring to the Batch Compilation, Recompilation, and Plan Caching Issues In SQL Server 2005 article on the TechNet site.)

The reference is well worth checking out, as it clarifies and documents an easily-avoided coding "strategy" that can lead to spurious recompiles.

Thanks for your kind words, Scott, and for the heads-up (which I'm more than happy to pass along).

     -wp