A Case of Shifting Identity

- OR - A BRIEF FORAY INTO THE REALM OF IDENTITY COLUMNS

We all know, or should know, when we insert into a table using using SET IDENTITY_INSERT, that the identity value will be reset to the next available value in the sequence. Meaning that the identity value will either be the next value that it was supposed to be (if we filled a gap in the sequence) or the next value larger than the row we’ve just inserted (so if we inserted 100, the next identity will be 101). Right? Well, sort of. Books Online, somewhat confusingly, states that “If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.” Define larger. In this case, larger actually does mean the next available value in the sequence. Check it:``` IF OBJECT_ID(‘tempdb..#t’) IS NOT NULL DROP TABLE #t;

CREATE TABLE #t ( id INT IDENTITY(1,1), val VARCHAR(10) );

INSERT INTO #t VALUES (‘a’); INSERT INTO #t VALUES (‘b’); INSERT INTO #t VALUES (‘c’); INSERT INTO #t VALUES (‘d’); INSERT INTO #t VALUES (‘e’); INSERT INTO #t VALUES (‘f’);

SET IDENTITY_INSERT #t ON;

INSERT INTO #t (id, val) VALUES (-1, ‘g’);

SET IDENTITY_INSERT #t OFF;

INSERT INTO #t VALUES (‘h’);

SELECT * FROM #t; “ZOMG, he’s so smart!” I know, that’s totally what you’re saying, right? Well, by using a straightforward example, I’ve gone ahead and proved nothing apart from showing you that I know how to use SET IDENTITY\_INSERT and copy/paste. So, what happens if you want to use a negative identity seed? Technically the numbers are getting smaller, not larger, right? IF OBJECT_ID(‘tempdb..#t’) IS NOT NULL DROP TABLE #t;

CREATE TABLE #t ( id INT IDENTITY(-1,-1), val VARCHAR(10) );

INSERT INTO #t VALUES (‘a’); INSERT INTO #t VALUES (‘b’); INSERT INTO #t VALUES (‘c’); INSERT INTO #t VALUES (‘d’); INSERT INTO #t VALUES (‘e’); INSERT INTO #t VALUES (‘f’);

SET IDENTITY_INSERT #t ON;

INSERT INTO #t (id, val) VALUES (1, ‘g’);

SET IDENTITY_INSERT #t OFF;

INSERT INTO #t VALUES (‘h’);

SELECT * FROM #t;