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:

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

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.