Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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.

share|improve this answer
    
It's already using the bigint data type, but the problem is not actually that it runs out, but that I want to restrict it to 6 digits, then reseed once it has reached 999999. –  tuseau Apr 20 '11 at 14:30
1  
@tuseau - I don't know of IDENTITY property to automatically reseed it's value, only manually, by truncating the table or by using the "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. Sorry I couldn't help. –  Marian Apr 20 '11 at 14:49
    
No probs, thanks for the input! –  tuseau Apr 20 '11 at 15:30

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.

share|improve this answer
    
I thought you needed admin permissions to run the DBCC CHECKIDENT? But thanks for highlighting Denali will have a Sequences feature (like the Oracle one)! I didn't know that, and yes, that's exactly what I'm trying to replicate. –  tuseau Apr 20 '11 at 15:33
2  
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 another option I did not mention: 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. –  Phil Helmer Apr 21 '11 at 7:08

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.

share|improve this answer
    
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 –  tuseau Apr 20 '11 at 15:27

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.
share|improve this answer
    
your method using check constraint is way better than the trigger. Thanks for the idea :-). –  Marian Apr 21 '11 at 7:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.