0

I'm using bigint identity columns on several tables and notice that they keep jumping from low numbers to high. In this case, from 17 to 1004 but in other tables into the 10000s. If this behavior continues I will be at the extents of bigint within a few thousand records! Why is this happening and how can I fix it?

Screenshot here:

1 Answer 1

0

Bigint ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. I think you've got a while before you hit the upper bounds - even if you're jumping up 100k records at a time, you could have a billion jumps and still not scrape the upper bounds. If you're still concerned, reseed your identity to start at the negative range and we'll all be dead before you have to worry about things.

Why does an identity value have gaps?

An application attempts to insert a record into your table and a number is removed from the pool. For referential integrity reasons the insert fails or the calling program then undoes (rollback) the transacted insert - either way, the identity value is no longer in the candidate pool. Yes, even if the transaction is rolled back, the number has exhausted and isn't rolled back.

Test for yourself

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Demo') IS NOT NULL
BEGIN
    DROP TABLE #Demo;
END

CREATE TABLE #Demo
(
    demoid int IDENTITY(1,1) NOT NULL
,   CantBeNull varchar(50) NOT NULL
);

-- add a record and look at our identity
INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('First Entry');
SELECT SCOPE_IDENTITY() AS GeneratedId;

-- GO forces the batch to commit (assuming default implicit transaction model)
GO
-- Violate RI
INSERT INTO #Demo
    (CantBeNull)
VALUES
    (NULL);
SELECT SCOPE_IDENTITY() AS RIViolationGeneratedId;
GO
-- add a record and look at our identity
INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('Third Entry');
SELECT SCOPE_IDENTITY() AS ThirdGeneratedId;
GO
-- Transaction consumes

BEGIN TRAN
INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('Fourth Entry');
SELECT SCOPE_IDENTITY() AS FourthGeneratedId;
ROLLBACK;

INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('Fifth Entry');

SELECT SCOPE_IDENTITY() AS FifthGeneratedId;
GO
SELECT D.* FROM #Demo AS D;

In the results tab, you'll see 1, 1, 3, 4, and 5 as that was the numbers being used but since we roll 4 back, the committed results of #Demo table will look

demoid  CantBeNull
1       First Entry
3       Third Entry
5       Fifth Entry

I don't like this behaviour

That's how the identity property works - there's no way to change it. The same holds true if you switch over to the Sequence object (new in 2012).

If the gaps are a concern, I'd look at identifying what process is consuming them. If it's referential integrity (RI) violations, tighten up the validation prior to attempting the save. If root cause is transactions being rolled back, then make it a two phase commit or something like that - Save to Stage.MyTable. If identity exhaustion is an issue, switch over to a SEQUENCE object and turn cycling on. Periodically, poll this table for committed transaction and then insert into dbo.MyTable from there. That should lessen the opportunity for gaps to exist in your surrogate key.

Kendra Little lovely isolation level poster helps me remember what isolation level I'd need for checking what's really, really committed.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .