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

BradleySQL gravatar image

BradleySQL
555 3 8 9

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

Tim gravatar image

Tim
36.4k 35 41 139

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

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

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][1]

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.

[1]: http://msdn.microsoft.com/en-us/library/ms177570(v=sql.90).aspx
more ▼

answered Mar 31, 2011 at 11:28 AM

Slick84 gravatar image

Slick84
1.3k 75 102 142

(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

Obs gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x34

asked: Mar 31, 2011 at 10:33 AM

Seen: 3813 times

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