Reset AutoNumbering

[lifegard2]

I am fairly new to SQL Server, so bear with me.

I just deleted all data from a table and now need to reset the autonumber field. Any ideas?

 

[raburns]

One way would be to delete the autonumber field in table design, save the table design, recreate the autonumber field. Hope this helps.

 

[tlbroadbent]

Use DBCC CHECKIDENT.

Example from SQL BOL: Reset current identity to 30 in jobs table
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[raburns]

I tried that in SQL Server 7, and it gave me a DBCC error about the arguments. Should this work in 7? Thanks in advance.

 

[mwolf00]

Terry,

I like the DBCC CHECKIDENT feature. Can't you reset the autonumber field by truncating the table? -----------------------------------------------------------------
DIM bulb
SET bulb = NEW brain

mikewolf@tst-us.com

 

[raburns]

Got it to work. Thanks.

 

[tlbroadbent]

mwolf,

Yes. Truncating resets the identity seed. Deleting does not. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.