SQL Server 2012 | Temp Tables are created with negative Object IDs
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.
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.”