Any workaround for rediculous identity reseed behavior? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Any workaround for rediculous identity reseed behavior?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-16 : 07:55:53

Thomas writes "Here's our scenario: We have a test database that we want to
wipe clean and reseed all the table identity columns before and after each automated test.

so, for each table before each test:
1. DELETE theTable
2. DBCC CHECKIDENT (theTable, RESEED, 1 )

Sounds simple.
However, here's the problem, taken from microsofts own documentation on DBCC CHECKIDENT:
"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."

This makes absolutely no sense!? Why does having data inserted since the table was created affect the next row id value *immediately after* a DBCC CHECKIDENT RESEED is issued? If i reseed to 1 I expect the first row inserted after the reseed to have id of 1, *always*. Not "1 if no rows were ever added since table creation" and "2 if rows were added since table creation." Again, makes no sense and seems like it would be a bug. Microsoft clearly doesn't think so as they plainly document this behavior yet offer no explaination for it's oddity.

I have not been able to find anywork arounds other than inserting a dummy record after table creation, deleting it and reseeding it all as part of "table creation", which is not acceptable. Truncate Table works as expected but you can only use it on tables with no foreign key constraints. so again not an acceptable workaround.

Can you offer a workaround? Or can you offer an explaination for this odd and inconsistent behavior?

Thanks for your help.
Thomas"

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 08:03:42

You'll get 1 whatever, won't you? Otherwise I'm not understanding the problem and perhaps you can post an example?


CREATE TABLE MyTable
(
	MyID	int IDENTITY (1, 1),
	MyOther	varchar(10)
)

INSERT INTO MyTable (MyOther) VALUES ('FOO')

PRINT 'Normal insert into fresh table'
SELECT * FROM MyTable
GO

DROP TABLE MyTable
GO
CREATE TABLE MyTable
(
	MyID	int IDENTITY (1, 1),
	MyOther	varchar(10)
)
GO
-- Reseed BEFORE any rows added
DBCC CHECKIDENT (MyTable, RESEED, 1 )
GO

INSERT INTO MyTable (MyOther) VALUES ('FOO')

PRINT 'Insert into fresh table which has first been reseeded'
SELECT * FROM MyTable
GO
DROP TABLE MyTable
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources