IDENTITY and IDENTITY_INSERT stuff

Here are a few gotchas for IDENTITY property on columns…hopefully you find one that you didn’t know about.

Did you know that if you try to set IDENTITY_INSERT on a table variable, it won’t work:

DECLARE @tbl TABLE(id INT IDENTITY, DATA CHAR(1))
INSERT @tbl (id, data) VALUES (1,'a')
GO

Msg 1077, Level 16, State 1, Line 2 INSERT into an identity column not allowed on table variables.

The action does work on temp tables of course, both local and global temporary tables. If you try to set IDENTITY_INSERT for more than one table at a time it gives you an error:

USE tempdb
GO
IF OBJECT_ID('test1', 'U') IS NOT NULL    
    DROP TABLE test1
CREATE TABLE test1 (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
GO
IF OBJECT_ID('test2', 'U') IS NOT NULL    
    DROP TABLE test2
GO
CREATE TABLE test2 (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
GO
SET IDENTITY_INSERT test1 ON
GO
SET IDENTITY_INSERT test2 ON
GO

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'tempdb.dbo.test1'. Cannot perform SET operation for table 'test2'.

Notice that if you SET IDENTITY_INSERT ON, you can’t tell if it’s already on; highlight the following and run this statement. I use the GO 100 to execute the batch 100 times:

SET IDENTITY_INSERT test1 ON
GO 100

Beginning execution loop
Batch execution completed 100 times.

There’s nothing that indicates to me…”Hey you idiot, it’s already turned on”. Same way for off:

SET IDENTITY_INSERT test1 OFF
GO 100

Beginning execution loop
Batch execution completed 100 times.

Same thing here. You don’t know whether it’s on or off by just running the command to set it ON or OFF.

Something to remember about IDENTITY_INSERT – don’t use this in some sort of online processing routine unless you are using it with temporary tables, because it only works for one table in the database. In tempdb, however, you can create tables within each SPID as well as use IDENTITY_INSERT in each as well. In other words, for temp tables, each connection allows you to use IDENTITY_INSERT. This may be handy for someone down the road.

I’ve looked high and low for any metadata or function that tells you what table IDENTITY_INSERT is set to within a database; can’t find one. I ran Extended Properties and didn’t' see it, and Googled it up as well. A few posts out there are also looking for one, but nobody seemed to find one either. Apparently there isn’t one, but I wrote a little procedure that might help tell you which table has the property set to ON. Run this:

CREATE TABLE t1 (id INT IDENTITY(1,1))
GO
SET IDENTITY_INSERT t1 on
GO

Now create the following. I’m capturing the message into a variable.

    
DROP PROC sp
GO
CREATE PROCEDURE sp
AS

IF OBJECT_ID ('dummy') IS NOT NULL
    DROP TABLE Dummy
    
CREATE TABLE Dummy (ID INT IDENTITY)

BEGIN TRY
    SET IDENTITY_INSERT Dummy ON
    IF @@ERROR =0
        SELECT 'No user table in database ' + DB_NAME() + ' is set to on.'
END TRY

BEGIN CATCH
    DECLARE @St VARCHAR (MAX)=ERROR_MESSAGE()
    SELECT  'Table with ident on is >>> : ' + 
        SUBSTRING(@St, PATINDEX ('%table%',@st )
          , PATINDEX ('%Cannot%',@st )-PATINDEX ('%table%',@st))
END CATCH
GO

Now run this to see how it works:

EXEC dbo.sp
SET IDENTITY_INSERT t1 ON
GO
EXEC dbo.sp

This little proc tells you which table has it turned on; if it’s off it lets you know as well.

Finally, run this:

DROP TABLE t1
GO
CREATE TABLE t1 (id INT IDENTITY(1,1), Data CHAR(1))
GO
SET IDENTITY_INSERT t1 ON
GO
INSERT t1 (ID, Data) VALUES (-2000000, 'a')
SET IDENTITY_INSERT t1 OFF
GO
INSERT t1 (data) VALUES ('b')
GO
SELECT * FROM t1

Notice that when you continue with IDENTITY insert, the next value inserted is 2, although you would think it would be a 1. Inserting –2000000 explicitly changes the order, and identity picks back up with 2.

Lee

-----------------------