What happens when Identity seed reaches an existent value in a primary key?

Question

I have an identity column that is also the primary key, of INT datatype. Due to the issue discussed here (cache loss), the identity has gaps and I chose to reseed to the previous value. In concrete terms, I have a situation that looks like this:

Table1

ID_PK    Field1
---------------
28       'd'
29       'e'
30       'h'
1029     'f'
1030     'g'

I looked around and couldn't find a clear answer to what happens when I make an insertion and the seed reaches the existent value that would break the constraint. Suppose I were to insert values 'x' and 'y' in two separated queries to the table, I can think of the following possibilities:

1. The identity will be reseeded before the first insertion and I will have both values inserted correctly.

2. The first insertion will fail, then the column will be reseeded, and only then the second insertion would succeed.

3. Neither will work and I will have to explicitly call DBCC CHECKIDENT to reseed before inserting values in the table

So, which is it? Or none of the above? Would this behavior be different if I inserted a multi-row result query into Table1? Thanks in advance

Answer 1

For completeness anyway, here's a script you can use to test:

USE Sandbox;
GO

CREATE TABLE test(ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, string char(1));
GO

INSERT INTO test (string)
VALUES ('a'),('b'),('c'),('d');
GO

SELECT *
FROM test;
GO

DELETE FROM test
WHERE string IN ('b','c');
GO

SELECT *
FROM test;
GO
DBCC CHECKIDENT ('dbo.test', RESEED, 1);
GO  
INSERT INTO test (string)
VALUES ('e'),('f');
GO

SELECT *
FROM test;
GO

INSERT INTO test (string)
VALUES ('g');
GO

SELECT *
FROM test;
GO
DROP TABLE test;

Running this script will give you the answer you need. If you wonder why I have used 1 as the RESEED value, this is explained in the documentation:

The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1.

In my script, this means that the next row to be inserted after the RESEED will have a value of 2 for its IDENTITY, not 1 (as rows already existing in the table (ID's 1 and 4)).

As several have said in the comments though, there's really no need to use RESEED on an IDENTITY column. If you need to maintain a sequence, you should (unsurprisingly) be using a SEQUENCE: CREATE SEQUENCE (Transact-SQL)

Answer 2

It depends:

Scenario 1

You get duplicates in the IDENTITY column, as no unique index or PK constraint.

create table I (
    id int identity(1,1) not null,
    i int null
)

Scenario 2

You get the following error as the inserted value conflicts with the Primary Key constraint:

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__I__3213E83FE0B0E009'. Cannot insert duplicate key in object 'dbo.I'. The duplicate key value is (11). The statement has been terminated.

create table I (
    id int identity(1,1) not null primary key,
    i int null
)

This proves that IDENTITY on it's own does not guarantee uniqueness, only a UNIQUE CONSTRAINT does that.

Answer 3

To close, turns out it's (2).

First insertion fails, reseed is automatic to the highest value, and only next insertion suceeds. Multi-value insertions behave the same if any of the values would break the primary key constraint.