November 9, 2010 | SQL Server

SQL Server v.Next (Denali) : Breaking change to #temp tables

Just a quick note on how Denali creates #temp tables, as the behavior has changed slightly from previous versions, and could potentially break your existing scripts. You may be familiar with this syntax for checking for the existence of #temp tables:

IF OBJECT_ID('tempdb..#foo') > 0
    -- do something

This always worked in previous versions of SQL Server, because the object_id values generated by the system were always positive.  In Denali, at least in CTP1, all #temp table object_id values I generate are negative.  Try this code on SQL Server 2005, or 2008, or 2008 R2:

CREATE TABLE #foo(bar INT);
 
IF OBJECT_ID('tempdb..#foo') < 0
    PRINT 'negative';
 
DROP TABLE #foo;
 
GO 100000

Then run this code on Denali:

CREATE TABLE #foo(bar INT);
 
IF OBJECT_ID('tempdb..#foo') > 0
    PRINT 'positive';
 
DROP TABLE #foo;
 
GO 100000

I'll bet you a donut you don't see any printout aside from:

Beginning execution loop
Batch execution completed 100000 times.

Meaning, no negative object_id values are generated on older versions, and no positive object_id values are generated on Denali.

So, as you prepare for the next version of SQL Server, you should verify that you won't get unexpected behavior due to this change. (I can almost guarantee that the breaking syntax that will not be caught by the Upgrade Advisor or Best Practices Analyzer.) I would instead use the following syntax:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    -- do something

3 comments on this post

    • Bart Czernicki - November 10, 2010, 8:06 PM

      I have always used the idiomatic "IS NOT NULL" check, if you are checking for positive/negatives something is not right.  (Got that from Henderson's great SQL book from 2002).

    • AaronBertrand - November 11, 2010, 3:38 AM

      Thanks Bart, yes best practice suggests using IS NOT NULL. But on customers' systems I see > 0 at least as often, if not more.

    • Victor M Sanchez - January 18, 2011, 4:45 PM

      Good hint Aaron, to keep in mind in scripts and SP migrations.
      Regards

Comments are closed.