x

Reset Identity Seed

I have been working in a development database on an ETL process where the table has an identity seed column. After running the ETL process multiple times truncating the table after each run I have noticed that my identity column is starting at a much higher number. It is acting like it is keep count of all the previous rows. How can I reset this to start at zero? I am working with SQL 2005 SP3.

more ▼

asked Mar 31, 2011 at 10:33 AM in Default

avatar image

BradleySQL
595 8 10 13

Thanks everyone. @Slick84 and @kevin feasel you are correct, I had been using delete tablename instead of truncate table tablename. Using truncate resets the identity seed too. @trad, thanks for your quick response and explanation as well.

Mar 31, 2011 at 12:42 PM BradleySQL
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

@BradleySQL, first of all the database isn't acting, it does in fact keep a tally of all the previous inserts by design. The simple way to reset (reseed) the identity column is to

DBCC CHECKIDENT('tablename', RESEED, 0)

With 0 being the number you want to start the identity back off with. For instance if you want the value of the next row to be 101, then replace 0 with 101.

I hope this helps.

more ▼

answered Mar 31, 2011 at 10:36 AM

avatar image

Tim
40.4k 39 83 166

Thanks for the explanation, this is exactly what I was looking for.

Mar 31, 2011 at 10:41 AM BradleySQL
(comments are locked)
10|1200 characters needed characters left
 dbcc checkident('[SchemaName].[TableName]', 'reseed', -1);

The next record inserted will have a value of 0.

Also, if you do not have any foreign keys pointing to this table, instead of just doing a "delete from [table]," you can truncate the table with:

 truncate table [SchemaName].[TableName]

This will reset the identity key to whatever you originally set it to on the initial creation.

more ▼

answered Mar 31, 2011 at 10:37 AM

avatar image

Kevin Feasel
6.2k 4 7 15

Thanks, no foreign keys on the table, I have been doing truncates after the ETL load when I needed to test it again. That is what got me in this situation of needing to reseed the table.

Mar 31, 2011 at 10:42 AM BradleySQL
(comments are locked)
10|1200 characters needed characters left

Your identity values should reset to zero automatically when doing a TRUNCATE. The reseeding procedure is only used when running a DELETE statement which does not reseed.

http://msdn.microsoft.com/en-us/library/ms177570(v=sql.90).aspx

FROM MSDN: If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

more ▼

answered Mar 31, 2011 at 11:28 AM

avatar image

Slick84
1.3k 75 104 147

(comments are locked)
10|1200 characters needed characters left

Are you saying you are issuing the truncate table statement but the identity column is not resetting to zero?

more ▼

answered Mar 31, 2011 at 11:10 AM

avatar image

Obs
21

Correct. My identity column keeps a running tally. Example, it started at zero, I insert 1000 rows, truncate the table and run the insert again, it starts at 1001. using the command @trad and @kevin feasel listed I can reset it to zero and start over. Not that it is critical to do so in my dev environment but I wanted to know how to do it.

Mar 31, 2011 at 11:12 AM BradleySQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x35

asked: Mar 31, 2011 at 10:33 AM

Seen: 4605 times

Last Updated: Mar 31, 2011 at 10:33 AM

Copyright 2016 Redgate Software. Privacy Policy