Function used to set autoincrement id to 0

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

General SQL Server Forums

New to SQL Server Programming

Function used to set autoincrement id to 0

Author

Topic

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-12 : 05:07:07

Hi all, i am deleting rows of a table and reinserting new ones. This table has an auto increment primary key. what is the function used to set it to zero each time i delete the rows in order to rebegin counting from 1 when i refill the table. Thanks in advance...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 05:13:12

If you are getting rid of all the rows in the table, then use Truncate instead of Delete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 05:17:57

You can also use DBCC CHECKIDENT to reset the identuty value.

E 12°55'05.25"
N 56°04'39.16"

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-12 : 05:21:46

My query is "delete dbo.WaitersCounts" Can you please explain to me exactly how to use these functions? thanks a lot

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 05:23:21

As I said, IF you are deleting ALL rows in the table then truncate table dbo.WaitersCounts

Or from Pesos example, DBCC CHECKIDENT(dbo.WaitersCounts, 1, reseed)

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-12 : 05:43:15

Thanks a lot Guys. I used the syntax "delete WaitersCounts
DBCC CHECKIDENT(WaitersCounts,reseed,0)"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 05:53:41

If you are going to delete all records, use TRUNCATE because of less logging.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 06:01:56