Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: One More Point On Recompilations and Temp Tables

Database Programming: One More Point On Recompilations and Temp Tables

  • Comments 2
  • Likes

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

Comments
  • One of the great things about my involvement with the SQL Ranger community is that I occasionally here

  • Binh Cao's customer left a comment on yesterday's post on global temp tables. Here's the money quote:

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment