SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

March 15, 2007 by pinaldave

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

DBCC CHECKIDENT (yourtable, reseed, 34)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 24 Comments

24 Responses

on May 17, 2007 at 10:59 pm | Reply Rajeev Rai

Thanks…The article was very helpful for me…Rajeev
on May 18, 2007 at 8:02 am | Reply Ron Moses

It should be noted that if you run DBCC CHECKIDENT (yourtable, reseed, seed_value) on a virgin table that has never had records in it, the next record added will receive an identity value of (seed_value), rather than (seed_value + 1). In other words, running DBCC CHECKIDENT (yourtable, reseed, 0) on a virgin table will result in the first record receiving an identity value of 0, not 1.

It has been suggested that running the statement a second time without the seed value will address this, but I have had mixed results with this method:

DBCC CHECKIDENT (yourtable, reseed, seed_value)
DBCC CHECKIDENT (yourtable, reseed)
on May 18, 2007 at 8:06 am | Reply pinaldave

Hello Ron,

This is excellent addition to original post. Thank you very much for your addition.

Kind Regards,
Pinal Dave
(http://www.SQLAuthority.com)
on August 4, 2007 at 12:21 pm | Reply mithilesh kumar

Hi I have tried this to set identity. I have faced some problem like:

I have a table “test” with 2 columns as id int identity 1,1 and name varchar(10)

i entered 5 records then run the query: DBCC CHECKIDENT (test, reseed, 34)

and then inserted 2 rows, it works but again I run this query

DBCC CHECKIDENT (test, reseed, 3)
then the next value in id column is 4 which is against the rule of identity. identity means unique. please correct me if i am wrong.
on August 10, 2007 at 5:14 am | Reply bipan

Can we generate alphanumeric values in sequence no in identical column?

Like I want my registeration for Gurgoan city should follow like:-
ggn1
ggn2
ggn3
.
.
.
.
.
.
ggnn
on November 28, 2007 at 9:40 pm | Reply charlie

Thanks
on January 15, 2008 at 4:23 pm | Reply SQL SERVER - Time Out Due to Executing DELETE on Large RecordSet Journey to SQL Authority with Pinal Dave

[...] will have to get the maximum identity before you truncate and reset it to that maximum identity. SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity . If you are not using identity or do not have use for identity you do not have to [...]
on January 16, 2008 at 3:05 pm | Reply Milos

Does DBCC RESEED works with replicated databases? Can I change the identity value on a subscription database?
on January 18, 2008 at 7:56 pm | Reply Hemant Nehete

Hi mithilesh kumar,
Identity columns does not stand for uniqueness,
Unless you don’t define identity column as part of Primary Key.
on February 20, 2008 at 8:09 pm | Reply Charl

How do I delete the content of a table or whole database???
on March 8, 2008 at 4:07 am | Reply Vladimir Kelman

We experience a strange problem. Identity field of a table is set to [1,1] by generating script. Every time, when we drop the whole database, and then re-create it by running script in SQL Server 2008 Management Studio, and then insert a record into that table programmatically (C# code), SQL Server set identity field record of that first record to 0 instead of 1!
Then we also programmatically delete all records from that table and then execute
DBCC CHECKIDENT(‘” + targetTableName + “‘, RESEED, 0)

Next time, the same C# function inserts records correctly, with identity field starting from 1 as expected.

So, again, identity field does not want to behave correctly if database was just created by script. After inserting a record and deleting it, everything works fine.

Is it SQL Server 2008 bug? Any idea on possible workaround?
on March 13, 2008 at 2:07 am | Reply Leo Espejo

MSDN is a good reference, but you are THE reference, thank god you exist !

Best regards,
Leo Espejo
on April 22, 2008 at 2:14 am | Reply Nick Sturdivant

Thanks to Dave for the OP and to Ron Moses for the virgin table observation. I had been troubleshooting this for some time. Glad to have found this post.

Thanks again,
Nick
on May 5, 2008 at 9:18 pm | Reply Mike S

I have a table designed by a FORMER employee. There was no primary key created. In the table there are 7500 some records. The values started @ 1 through 2145, then 1600 columns are null. Then the index begins @ some random number and continues ASC. Is there a way for me to just reset the FamilyFriendSurveyID column in the entire table?, I need to now make this column be a primary key for the table, can this be done to auto increment starting @ 1? Thank you in advance for your help!!!
on June 4, 2008 at 9:23 pm | Reply Noshiri

Hello have a table in my database with value like

AAABBB<
1111<
1222<
1333<
2444<
2555<
2666<
3777<
3888<
3999<

i want an sql query that display distinct value from column AAA and its MAX value from column BBB
as shown below. Thanks in advance for your help!!!
1333<
2666<
3999<
on July 5, 2008 at 12:28 pm | Reply dakshinamurthy

Hi,
please let me know what will be the maximum seed value for a table which is of bigint. I would also helpfull if i get to know what to do if we exceed the seed value for a bigint field in a table
on November 7, 2008 at 5:33 am | Reply Raj Parmar

Excellent article.. reliable as always and saved me a lot of time! Thanks.
on November 12, 2008 at 2:45 am | Reply KaraK

Vladimir

if ur table it’s new u must use:

DBCC CHECKIDENT (yourtable, reseed, seed_value)

but if u just delete that table u can use:

DBCC CHECKIDENT (yourtable, reseed, 0)

CYA
on December 1, 2008 at 7:07 pm | Reply Ari

I was dropping and recreating tables for my unit (integration) tests and came across this reseed problem for virgin tables. My (ugly) fix was to 1) insert row into the table 2) delete all rows 3) reseed 4) insert (again) into the table. This way I could know for sure what id the inserted row got.
on February 24, 2009 at 5:36 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity [...]
on June 30, 2009 at 1:27 pm | Reply Chris

Charl,
Please delete content of a table by issuing any of the following SQL statement:

DELETE FROM Table_Name;
TRUNCATE TABLE Table_Name;

Replace Table_Name with the actual table whose content you want to delete.

Deleting a whole database means that you will need some privileges, that is database admin privileges or database owner privileges.

To delete a database, issue the following SQL statement:
DROP DATABASE Database_Name;

Regards,
Chris Musasizi
on August 6, 2009 at 5:42 pm | Reply Luis A.

Thanks!! I had this problem always, it was so helpful!!
:)
on September 16, 2009 at 12:31 pm | Reply Jaroslaw Dobrzanski

What I wanted, short and simple described.
Thanks!
on October 2, 2009 at 9:23 pm | Reply Dave Sundell

How can we reseed or use the DBCC CheckIdent on a table with the prefix of a link server name. I get a syntax error when prefixing the table location with a link server name, e.g. DBCC CHECKIDENT ([LinkServerName].DBName.dbo.[tableName], RESEED, 0)