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;

Witchcraft! I made the numbers decrease instead of increase! But then I used good magic to put a positive number in there and then the witches corrupted the sequence and the numbers got smaller again!

Sensibly speaking, though: it works. The identity resumes the normal sequence and order is again restored in the world. Had I inserted -10 instead of 1, the next value inserted into the id column would have been -11. Which makes sense.

Why the linguistic confusion on the part of Books Online? I don’t know. I would hazard a guess and say that it was implied that they meant “larger in relation to the IDENTITY seed that you have set up” (and probably muttered the words “you pedantic toad” under their breath). But rather than explicitly go out on a limb and define such a thing in the documentation for SET IDENTITY_INSERT, they assumed that people would infer the meaning. You know, they assumed that people would make sense.

Why would you ever want to do something silly like this? I have my reasons, but I’m sharing this as a test of the application functionality that I needed and a useful demonstration of how IDENTITY works.

Menu