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.
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
Leave a comment
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 
Why cant we use if exists(object_id(‘tempdb..#temp’)) is null…drop table #temp
You can use it as, either:
if exists(object_id(‘tempdb..#temp’)) drop table #temp
OR
if object_id(‘tempdb..#temp’) is not null…drop table #temp
~Manoj
Re: Why cant we use if exists(object_id(‘tempdb..#temp’)) is null…drop table #temp
As mentioned by Manub22, there are two ways to drop a table, however, I want to put something out and make some slight changes:
if exists(select object_id(‘tempdb..#temp’)) drop table #temp –> Added ‘select’ here
OR
if object_id(‘tempdb..#temp’) is not null drop table #temp
OR
if object_id(‘tempdb..#temp’) is not null BEGIN drop table #temp END
OR
DROP TABLE IF EXISTS #temp –>Starting with SQL SERVER 2016
Hi,
I had a question on Global Temp Tables. It has been said about these tables that:
A global temporary table, ##table_name, exists for the duration of a user session or the procedure that created the table. When the last user session that references the table disconnects, the global temporary table is lost.
I used a stored procedure to create a global temp table and populate it with values. I was then able to access this table and its data from different user sessions.
I then dropped the procedure that created the global temp table and noticed that I was still able to access the global temp table and its data from different user sessions.
If this works, then how is it true that the tables exist only for the duration of the procedure that created the global temp table.
Could you please clarify.
Regards,
Divya
After execution of an SP only its batch is completed but the session still exists. You need to close the session, i.e. the window from where you executed the SP.