posts - 219, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

DBCC CHECKIDENT weirdness

DBCC CHECKIDENT is quirky.  If you run it to reset an identity column such as

DBCC CHECKIDENT (jobs, RESEED, 30)

it acts differently depending on whether you've ever had data in the table.  Books Online says

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1.

I have a script that does a pretty extensive data load.  In order to rerun it, I have a series of DELETE statements followed by DBCC commands.  This works great until you run it as part of a build.  Now it's running on clean tables and starts all the identity values at 0.   Grrrr.

Print | posted on Tuesday, October 21, 2003 10:04 AM | Filed Under [ SQL Server Stuff ]

Feedback

Gravatar

# re: DBCC CHECKIDENT weirdness

The following works for both virgin and cleared tables:

DELETE mytable;

SET IDENTITY_INSERT mytable ON;

INSERT INTO mytable ([key], data) VALUES(1, 'some stuff');

DBCC CHECKIDENT('mytable', RESEED);

SET IDENTITY_INSERT mytable OFF;

INSERT INTO deleteme (data) VALUES('more stuff');
7/12/2004 6:00 AM | Steve
Gravatar

# re: DBCC CHECKIDENT weirdness

Ooops: The last line should have read:

INSERT INTO mytable (data) VALUES('more stuff');
7/12/2004 6:01 AM | Steve
Gravatar

# re: DBCC CHECKIDENT weirdness

tu
12/7/2005 7:19 AM | rahul
Gravatar

# re: DBCC CHECKIDENT weirdness

It's been several years since this post was made, so this may be very old news. But, I just came across it, and for anyone else that does, here's the easiest way I found to fix this issue - use 2 CHECKIDENT calls:

DBCC CHECKIDENT('mytable', RESEED, 0)
DBCC CHECKIDENT('mytable', RESEED)


That should do the trick.
6/15/2006 11:18 AM | Robin Curry
Gravatar

# re: DBCC CHECKIDENT weirdness

Thank you Robin
7/14/2006 4:03 AM | Kevin
Gravatar

# re: DBCC CHECKIDENT weirdness

Thank you very much! That has been very useful!
3/26/2007 4:28 AM | ripe
Gravatar

# re: DBCC CHECKIDENT weirdness

Unfortunately, the method Robin cited does not appear to work in SQL Server 2005. I've tested this extensively, and I still get a 0 in my identity column for virgin tables. So I need to figure out one of two things:

1) How to tell if a table is virgin (has never had a record added to it) so I can set the seed value to 1 for virgin tables, or...

2) How to tell what the *real* current identity value of the table is. If you run the following in 2005:

DELETE FROM mytable
DBCC CHECKIDENT('mytable', RESEED, 0)
DBCC CHECKIDENT('mytable', RESEED)
SELECT IDENT_CURRENT('mytable') AS CurrentID
SELECT IDENT_SEED('mytable') AS SeedID

You will get CurrentID = 0 and SeedID = 1 regardless of whether the table is virgin or not.

So does anyone have any suggestions?

thanks!
Ron Moses
5/18/2007 11:21 AM | Ron Moses
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET