SQL with Manoj

These days I’m working on SQL Server upgrade from 2008 R2 to 2012 for one of our project module.

Today while working on it I got blocked while installing a Build. The build was failing with following error:

Error SQL72014: .Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 115 There is already an object named ‘#temp’ in the database.

I checked the code and found the line where it was failing:

IF object_id('tempdb.dbo.#temp') > 0
       DROP TABLE #temp

I checked this code with SQL Server 2008 R2 and it was working perfectly.

So to check and validate this I created a temp-table on SQL Server 2012 and found that it is created by negative Object ID, check this:

This is a new change done with SQL 2012 version, but this is not mentioned anywhere in MSDN BOL.

So, to make this legacy code work we have to re-factor all such cases, by:

IF object_id('tempdb.dbo.#temp') IS NOT NULL
       DROP TABLE #temp

Confirmation form Microsoft SQL team blog [CSS SQL Server Engineers]:
“in SQL Server 2012, we made a conscious change to the algorithm so that objectids for user-defined temporary tables would be a particular range of values. Most of the time we use hex arithmetic to define these ranges and for this new algorithm these hex values spill into a specific set of negative numbers for object_id, which is a signed integer or LONG type. So in SQL Server 2012, you will now always see object_id values < 0 for user-defined temp tables when looking at a catalog view like sys.objects.”

More Info on: http://blogs.msdn.com/b/psssql/archive/2012/09/09/revisiting-inside-tempdb.aspx