Home > SQL Errors, SQL Server 2012, tempdb > SQL Server 2012 | Temp Tables are created with negative Object IDs

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

  1. March 26, 2015 at 5:01 am

    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

    • March 26, 2015 at 6:52 pm

      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.

  2. sateesh
    February 1, 2015 at 4:00 pm

    Why cant we use if exists(object_id(‘tempdb..#temp’)) is null…drop table #temp

    • February 1, 2015 at 4:26 pm

      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

  1. January 24, 2016 at 1:54 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: