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.