SQL Server IDENTITY column automatic re-seed

Question

I work with huge datasets. Many of the transactions that take place in my database are enormous - trillions of rows, and more.

Some of the tables use IDENTITY columns, not for unique IDs, just because its simple and fast, and to provide a concurrent solution to providing an incrementing number. However, when the IDENTITY column reaches its limit, I want it to automatically reseed immediately within the statement when it reaches its limit. I appreciate this is odd behaviour for most, but it would make sense to at least have this functionality as an option, surely? You can't even do a reseed within a transaction, and I cannot use truncate (don't want to delete).

Why is this not possible? Has anyone else come across this as a problem before?

Answer 1

You can use the bigint data type in order to avoid reaching the max value too often. The range for bigint is -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807), storage = 8 Bytes. Are you often out of this limit?

In case you want to reseed you should be able to run the "dbcc checkident" statement inside a transaction, but you'll have to do more error handling inside your code.

Answer 2

I am assuming you have a business-based need to do this as it is not an optimal thing to do.

To do what you ask, use a loop with TOP 999999 for the INSERT. For example,

SET ISOLATION_LEVEL SERIALIZABLE;

CREATE TABLE #AlreadyAdded (MyPK int);

--"Prime" the loop
SELECT 0;

WHILE @@rowcount > 0
BEGIN;
    BEGIN TRANSACTION;

    <get the current gap (@GapValue) between 999999 and TargetTable.IdentCol>

    INSERT INTO TargetTable (PKColumn, col1, col2, ...)
        OUTPUT inserted.PKColumn INTO #AlreadyAdded (MyPK)
        SELECT TOP (@GapValue) PKColumn, col1, col2, ...
        FROM   SourceTable AS st
        WHERE  NOT EXISTS (
                       SELECT  0 
                       FROM    #AlreadyAdded AS aa 
                       WHERE   st.PKColumn = aa.MyPK
               )
    ;


    --Reset the identity column to 0 or 1
    DBCC CHECKIDENT ...


    COMMIT TRANSACTION;
END;

I don't know how much concurrency you need while this transaction is running. If you need for your INSERTS to occur contiguously, then move the transaction begin/commit pair outside the loop.

All that said, if you can wait for Denali, the new Sequences feature (new to MSSQL, that is) is exactly what you're looking for. It can have an artificial upper bound and cycle automatically.

Answer 3

Tuseau,

Let me ask this. What's the reasoning behind on what you are trying to do? Good programs are always kept very simple and NOT complicate them. I think you are complicating this and you shouldn't try what you explained above.

Can you share the reasoning behind this so that we understand?

Once you use the data type BIGINT, even if the values are small and fit into the INT range, SQL Server will use the sam space as a BIGINT would. You don't gain disk space here.

Answer 4

To answer your questions:Why is this not possible? I feel this is not a conventional requirement of a RDBMS. Has anyone else come across this as a problem before? I have not needed this functionality before. That being said, there will be a trade off between performance and availability required to implement this.

• You can use Marian's method outlined in the comments to implement this, but there will be a performance hit due to the triggers firing on every insert.
• If availability is not an issue, you can use a CHECK constraint to ensure that the IDENTITY does not hit 999999. Once it does, manually reseed it using DBCC checkident (as mentioned by Marian), or you can create a SQL agent job to check in intervals and reseed as required.