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 it's 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?

Here's the functionality: In SQL Server I have a table that acts as a sequential number generator, like a Sequence in Oracle. The maximum we want the number to be is 999999, after that, reset to 0. This number is added on to some other fields (one of them a datestamp) to generate reference numbers.

The system is highly concurrent and I need it to be fairly obvious when the reference number was generated. As it stands, there is a task that runs every day to reseed the IDENTITY column, but due to the large number of records daily, if there are > 999999 records processed, I get an error.

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.

I don't know of an IDENTITY property to automatically reseed its value, only manually, by truncating the table or by using dbcc checkident. You could create a trigger on your table and once the last inserted value gets close to the maximum then you'd be able to reseed. But this will probably add cost to your transactions.

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.

Regarding permissions for DBCC CHECKIDENT: According to BOL, the caller must own the table, be in the db_owner db role, be in the db_ddladmin db role, or be in the sysadmin server role. So, if none of those apply, you could also look at using EXECUTE AS <table owner>.

There is also another option: You could forego the IDENTITY column property and roll your own sequence generator. You could also create transaction-safe methods for working with it. Just like identity, you could still end up with gaps, so that aspect is no better nor worse. I think testing/maintenance considerations would decide that one.

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

Answer 3

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.

Answer 4

Instead of reseeding the identity, define the identity column as a BIGINT, then use a computed column to obtain the last 6 digits as the number between 0 and 999,999:

CREATE TABLE dbo.SomeData
(
    ID bigint IDENTITY(1,1)
    , Digits AS CAST(RIGHT('000000'+CAST(ID AS VARCHAR(20)), 6) AS int)
);

You should realize that the last 6 digits are a repeat between 0 to 999999. This is how math works.

00000999999
00001000000
00001999999
00002000000