The good RESEED

Identity fields in Microsoft SQL Server are a great tool for bringing order to chaos, providing a nice, predictable key to data that doesn't necessarily lend itself to a natural keys.  The problem is that, like taxis at a rank, you always have to take the next one that comes along.  This can be a problem for SQL Dabblers who want to bump the identity field up to a particular value.  Maybe you want all records created after a certain date to start at a particular nice round value, for instance.  Or you want to reserve particular values for testing or reference data.

Through the endless power of DBCC, you can do this.  The syntax is

DBCC CHECKIDENT ([yourtablename], reseed, [new value you want to start at])

(incidentally, Dabblers who are not familiar with DBCC should take some time to look into it.  It's exceptionally powerful, and rather dangerous - essentially the equivalent of opening SQL Server's casing, and hitting the innards with a spanner.)

So, that's all you need to know, right?

STOP!

As I mentioned, DBCC is powerful and dangerous, and the reseed operation is no exception.  Let's dabble in some code, and see what happens.


create table identitytest (ID int identity, vvalue int)
go
insert into identitytest (vvalue) VALUES (101),(102),(103)
select * from identitytest

The results here are what we expect.  The identity field starts at 1, and increments each time by 1, which is the default.


DBCC CHECKIDENT (identitytest, reseed, 10)
insert into identitytest (vvalue) VALUES (104) 

we've reset the identity value to 10, so the inserted record should have an ID of 10, right?

D'OH

select * from identitytest

11, eh?  So the thing to take away here is that after a reseed, the next inserted record gets an identity value of the reseed value + 1.  That's why we get 11 rather than 10.

BUT

This is only true for tables that have, or have had, records in them.  For a new 'virgin' table, the reseed behaves differently

create table identitytest2 (ID int identity, vvalue int)

DBCC CHECKIDENT (identitytest2, reseed, 10)

insert into identitytest2 (vvalue) VALUES (100)

select * from identitytest2

So, that only works with a brand new table, right?

NO!

Yeah, we're not done yet, dabblers.  Using TRUNCATE on a table causes SQL Server to view it as a 'new' table for the purposes of reseeding identity values.


truncate table identitytest2
DBCC CHECKIDENT (identitytest2, reseed, 20)
insert into identitytest2 (vvalue) VALUES (200)
select * from identitytest2

And you can see that we get 20, rather than 21.

So, to summarize,

DBCC CHECKIDENT ([tablename], reseed, x) means that the next row inserted will have an identity value of x+1 EXCEPT if the table has never had any rows in it EXCEPT if you truncate it first.  Have fun dabblers.

BUT HANG ON...

If you're following along on Microsoft SQL Server 2000 (no shame in that) then you're probably scratching your head right now (actually, you're probably calling me rude names, but I like to think well of my Dabblers).  This is because all the above is true only on later versions of SQL Server.  On 2000, reseeding will always give the next record the identity value of whatever you reseed as.

I've no idea what happens for those still using versions before 2000.  You guys really are weirdos :-D.