My SQL Server table identity is incrementing way too fast

Question

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?

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

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.

Example:

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.

• ScottC: 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
• spaghettidba: 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)
• ScottC: I've been thinking today of how I would do something like that, I will definately look into this, thank you