Search code examples
sqlsql-serversql-server-2012temp-tables

SQL Server 2012 temp table OBJECT_ID issue

We have an issue upgrading to SQL Server 2012. I am using the following script to create temporary tables that used to work fine on SQL Server 2008 R2 but now it is generating an error with 2012:

if (OBJECT_ID( 'tempdb..#idstable') > 0)
    truncate table #idstable
else
    create table #idstable (id int not null)

the error thrown is

There is already an object named '#idstable' in the database.

This is obviously not thrown the first time I use the script (in the same transaction).

Any idea? Thank you!

Solution

  • In SQL Server 2012, #temp tables are created with a negative object_id, so your script won't work as is. The safest way is:

    IF OBJECT_ID( 'tempdb..#idstable') IS NOT NULL

    (I blogged about this here, and knew it would catch someone.)

    Though your script is bound for failure anyway, if it is part of a single batch. The parser will not let you try and create the same #temp table twice.