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 the 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 (https://blog.sqlauthority.com)
85 Comments. Leave new
Thanks…The article was very helpful for me…Rajeev
Pinal,
I have an issue. I have hosted a web application with sql serve db in a central server in state capital and there are 10 other districts here. every client machine is accessing the apllication through the web. but if there is no connectivity they will enter the data into the local serve within the district and this data will be updated to the Central server whenever the connection establishes.
The problem is every table is having an identity auto increment column which is primary key and this key is referencing as foreign key in the transaction tables. so at the time of synchronisation the identity will get affected and the transaction data will get affected worse. How can I override this situation. Please help on this
HI Hari,
This is a common problem if you are using an int as your identity column. A better approach would be to use GUID, this is a unique value that can be entered against each row. You can see what it looks like by writing ‘Select NewID()’ This will show you an example of a GUID. The data type is known as UniqueIdentifier in the SQL table.
Hope this helps.
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)
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.
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
Thanks
Does DBCC RESEED works with replicated databases? Can I change the identity value on a subscription database?
Hi mithilesh kumar,
Identity columns does not stand for uniqueness,
Unless you don’t define identity column as part of Primary Key.
How do I delete the content of a table or whole database???
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?
Vlad,
I would first run sp_help YourTable and look for the value of Seed. If it is 0 research stops here.
If it is 1 use profiler to trace what you C# code does. There is a chance it sets identity insert on and inserts into your identity column explicitly.
Next, try to reproduce the problem inserting into the script-generated table from SSMS. Reseed, repeat.
BTW, note that Reseed does not change the original seed value, therefore if you need to reseed do it after each truncate.
good luck,
–yul
MSDN is a good reference, but you are THE reference, thank god you exist !
Best regards,
Leo Espejo
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
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!!!
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<
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
Excellent article.. reliable as always and saved me a lot of time! Thanks.
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
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.
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
Thanks!! I had this problem always, it was so helpful!!
:)
What I wanted, short and simple described.
Thanks!