SQL Server IDENTITY column automatic re-seed

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.

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.

StackExchange Database Administrators Q#2279, answer score: 3