SQL Server Identity Column – How to Manage Fast Incrementing Values

auto-incrementsql-server-2012

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?

Best Answer

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.

Related Solutions

Postgresql – Creating sequence for longer numeric values

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo 
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.

SQL Server – Identity Value Jumps When Restarting

I found that it is a new feature (don't know what is use of it)

Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs only the allocation of a batch of identity values. The allocated range is cached and issued on demand until a new batch of values is needed.

If SQL Server is restarted without the database containing the identity object being checkpointed on shut down, any remaining unused values in the cached range are lost, resulting in a jump in the values on restart.

Unfortunately, common ways of shutting down SQL Server 2012 do not currently automatically checkpoint databases (this contradicts the documentation, so it should be fixed at some point in the future). To avoid this particular cause of jumps in assigned identity values on SQL Server 2012, always shut down SQL Server using the T-SQL command SHUTDOWN (without the NOWAIT option).

The SHUTDOWN command will checkpoint all user databases correctly before shutting down the server. Do not use the Windows Service Control application, SQL Server Configuration Manager, the SQL Server Management Studio UI, or any other method.

You could also manually CHECKPOINT all databases before shutting down the SQL Server using any other method, but this requires you ensure no identity-allocation activity occurs in any database after your checkpoints, and before the shutdown completes. This may not be easy to achieve reliably.

More information and background in Lost Identity by Kalen Delaney.

Trace flag 272 is now documented in DBCC TRACEON - Trace Flags (Transact-SQL):

Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. Note that identity caching is used to improve INSERT performance on tables with identity columns.

Note: Starting with SQL Server 2017, to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Scope: global only

Best Answer

Related Solutions

Postgresql – Creating sequence for longer numeric values

SQL Server – Identity Value Jumps When Restarting

Related Question