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.