I have a table with pretty basic design: 1 primary key (id), 1 unique column (filename) (the filename of the file I am tracking)

The primary key is an identity column (bigint) (I had to make it bigint recently because it's growing so fast)

There are only 82318 rows in the table but the identity for the last row I inserted is 2147891928.

Identity spec is on, starts at 1 increment by 1

If I go through the table ordered by id DESC I can see it jumped from time to time, sometimes by 40, sometimes by 100, 1000, 10000 or more. Is this related to the SQL Server restart seed "FEATURE" in SQL Server 2012? Because it sounds slightly different

I have a script that runs and tries to insert filenames into the SQL from a folder, if the filename already exists nothing should happen because its setup as unique right? or does it use an auto increment digit?

  • How many rows are inserted and deleted daily? Or attempted to insert? Jul 3, 2015 at 15:25
  • You might be hitting bug in SQL server 2012 - enabling tf 272 will help.
    – Kin Shah
    Jul 3, 2015 at 16:00
  • @Kin - That isn't a bug. Just a change in behaviour. Seems unlikely though - would need 2 million service restarts to overflow an int. Jul 3, 2015 at 16:50
  • I know this is a really late reply but to @MartinSmith I'd say anywhere from zero some days to 50 - 150 other days
    – ScottC
    Dec 22, 2015 at 17:14

1 Answer 1


Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway.


IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL 
    DROP TABLE #test_identity;

CREATE TABLE #test_identity (
    i int identity(1,1),
    name varchar(50) UNIQUE

INSERT INTO #test_identity (name) VALUES ('aaaa')
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 1 row inserted, ident_current = 3
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('aaaa') --fails
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 0 row inserted, ident_current = 5
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('bbbb')

-- 1 row inserted, ident_current = 6
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('aaaa') --fails
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 0 row inserted, ident_current = 8
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('cccc')

-- 1 row inserted, ident_current = 9
SELECT * FROM #test_identity

The SQL Server 2012 jump in identity columns might be part of your issue, but I guess it has a minor impact on what you're seeing.

Identity values are not guaranteed to be contiguous by design, so if you're relying in "no holes" in your identities, you may want to reconsider your assumptions.

  • 1
    I'm not relying on no holes per se, I select on specific id's not by counting or anything what I am relying on is that I don't hit the ceiling of int (or now in this case bigint) so early, my VBScript stopped running overnight when the ID column hit 2,147,483,647 which seems SOOOO high for 82,000 rows but if I am using up an increment on every failed insert it makes sense
    – ScottC
    Jul 3, 2015 at 15:25
  • You could use a MERGE statement to insert your rows or check if the row exists with INSERT SomeTable SELECT FROM Values WHERE NOT EXISTS (SELECT FROM SomeTable WHERE condition) Jul 3, 2015 at 15:29
  • I've been thinking today of how I would do something like that, I will definately look into this, thank you
    – ScottC
    Jul 3, 2015 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.