SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Identity Values – Two things to know

I saw someone post a question recently about running out of identity values in an INT column. It happens, it’s happened to me, and I’m not surprised. They couldn’t go to a bigint because of their application, so they asked if they could “renumber” the identity column.

Of course you can, and it’s simple with DBCC CHECKIDENT and the RESEED parameter. As I  and others explained that, the person seemed to think that this would renumber his data and he’d be fine.

Uh,  no.

There are two things you need to know about the identity.

1. They only work on the next value being inserted.

2. They have nothing to do with current values in the table.

A lot of people think that auto number means that the server tracks what’s in the table and then bases the next number on that. Not so. Just as many people have done when they’ve stored the “next” value in another table and used that in their own autonumber scheme, that’s what SQL Server does. They have a better implementation than you get in T-SQL and they handle large sets of rows inserted at once better, but they still just track what the next value to be inserted is. Try this:

CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
DBCC CHECKIDENT( mytable, RESEED, 1)
GO
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
DROP TABLE dbo.MyTable

What are the results?

You might expect 2 1s, but you get this:

ident_a

If I change the reseed parameter to 0, then I get this:

ident_b

So you can see that the “last” value is being stored for the identity column. However note that there’s no checking of what’s in the table? The reseed merely changes that “last” value and so when the database engine goes to insert a new row, it increments this value, regardless of what’s in the table.

If I wanted to prevent duplicates, I’d need a unique index. Nothing to do with the identity property.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Posted by Giammarco Schisani on 6 January 2010

Very interesting! Thanks for sharing.

Posted by peter on 8 January 2010

Two more things to know:

1. Every failed insert still increases the seed value. This is why you can get "gaps" in your identity values. The seed value increase is done before the server encounters the error and seeds themselfs are not part of the transaction and thus do not get rolled back.

2. When you do identity_insert (you force an ID, instead of letting the seed handle it), SQL Server 2005 (not sure about 2000) will up the seed as needed. Lets say a regular insers would lead to ID 5, but you do an identity_insert with a value of 10 specified for the identity column, the next regular insert will get ID 11, not 5 or 6. The rule is simple, a manualy inserted ID greater then the SEED, will cause the seed to be increased to match the ID and upon next insert SEED + 1 is generated for the identity column.

Posted by JMasciantoni on 8 January 2010

Good points.  Also consider that the IDENTITY values are likely being used in related tables for linking and that any renumbering can be catastrophic.  For a solution to the original question that Steve mentions I've used the following. 1) create a new IDENTITY column in the table, leaving the old IDENTITY as just an int value. 2) Write a script replacing all of the old IDENTITY references in other tables with the new IDENTITY value. 3) leave the old IDENTITY value in place for a few weeks in case Murphy shows up :-).

Thanks,

John Masciantoni, Florida Health Care Plans, Flordia.

Posted by DarrenJenkins on 8 January 2010

Also remember that the INT range actually starts at -2,147,483,648

By starting at the beginning of the range and not the middle (0 or 1 as more common) you effectively have doubled the number available

Posted by Charles Kincaid on 8 January 2010

John, you could set your Foreign Keys to CASCADE and then renumber the main table.  Just hope you have the log space and remembe to wrap ina transaction.

Darren,  thanks for pointing that out.  I was going to use BIGINT.  I stil am but you gave me an excuse to "go negative".

Posted by terrance.steadman on 8 January 2010

Greetings,

I find it interesting that SQL Server does not have an unsigned int equivalent to the int data type.  This would increase the number of positive values allowed to 4,294,967,296 for the IDENTITY field.  As far as I know, most people don't use the negative values of the IDENTITY field anyhow.  This would also make it a lot easier to keep the values in the database as the binary value is still the same, it is just the interpretation of the data type that changes.

Thanks for your time.

Posted by Dave on 8 January 2010

So, if I seed the IDENTITY with -2,147,483,648, is the next one inserted -2,147,483,647?

Posted by bill.thrasher on 8 January 2010

David -

Yes, if the second parameter is a 1.  The second value specifies how much (and direction) to increment. So you could define the id on an errors type table as INT IDENTITY(-1,-1)  -  start with -1 and go backwards.

Posted by geerobg on 8 January 2010

Also, a rolled back transaction will still cause the identity value to advance.

create table _X(id int identity(1,1) not null, another int)

insert _X default values

select * from _X

begin tran

insert _X default values

rollback tran

insert _X default values

select * from _X

Posted by mstjean on 8 January 2010

Before you try reseeding to a negative value, run a few tests someplace safe on any application that's hitting the db. We thought we were being very clever when we tried using negative values for testing data. Can't tell you why, but the app choked.

Posted by Ayyappan on 9 January 2010

Very nice explanation

Posted by Paul Kohler on 10 January 2010

Nice list of things to remember with IDENTITY.

One thing I thought I would point out with regards to the use of negative ID values - I have worked in several places where a negative ID indicated that the record was "new" and would be inserted as opposed to updated (usually there was an "AddEditFoo" type stored proc).

It's definitely something to test out!

PK  :-)

Posted by Jan Van der Eecken on 11 January 2010

What actually happens if the IDENTITY column reached its maximum and one tries to insert another row? Does it just fail, or does it just wrap around into negative territory? Would have tried it out myself but have no access to a SQL box while I'm on leave.

Greets,

Jan

Posted by Terry Gilman on 11 January 2010

You'll get an integer overflow error.

Posted by Michealia VanScoy on 11 January 2010

This scares me.

Posted by Tom Garth on 11 January 2010

bill.thrasher said:

David -

Yes, if the second parameter is a 1.  The second value specifies how much (and direction) to increment. So you could define the id on an errors type table as INT IDENTITY(-1,-1)  -  start with -1 and go backwards.

January 8, 2010 1:10 PM

~~~~~~~~~~~~~~~~~~~~~~~~

So in the original case, you could change the increment to -1, then reseed to 0 or -1, and use the other half of theavailable values.

Leave a Comment

Please register or log in to leave a comment.