SQL Server 2012 : 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. Starting with SQL Server 2012, 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 SQL Server 2012:
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:
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 SQL Server 2012.
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 promise that the breaking syntax 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