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)
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)
Hello Ron,
This is excellent addition to original post. Thank you very much for your addition.
Kind Regards,
Pinal Dave
(http://www.SQLAuthority.com)
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
[...] 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 [...]
pinal got it just
Earlier it is : DBCC CHECKIDENT (schools.tblCaste,reseed,0)
Success: DBCC CHECKIDENT (‘schools.tblCaste’,reseed,0)
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.
[...] SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity [...]
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!
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)
Hi Dave,
It’s worth notting that DBCC commands should only be used on a local server.
If you tried a command like this: DBCC CHECKIDENT (‘LINKD_SERVER.DB.SCHEMA.TABLE’, NORESEED); then you will get the following error from SQL server:
Server: Msg 2501, Level 16, State 2, Line 1
Could not find a table or object named ‘LINKD_SERVER.DB.SCHEMA.TABLE’. Check sysobjects.
I recommend you to run DNCC CHECKINDENT on the table by connecting directly to the SQL instance that manages the database where your table is.
It is however possible to run DBCC commands outside a database but on the same instance as follows:
DBCC CHECKIDENT (‘DB.SCHEMA.TABLE’, NORESEED);
I hope this answers your query.
God bless,
Chris Musasizi
this is cool.!
Thanks.. it helpful
Hi,
DBCC CHECKIDENT (yourtable, reseed, 0) will provide different results. Based on my experience, when data is deleted with DELETE FROM table then the first record will have 1 in its identity column. Howver, when table is wiped out with TRUNCATE clause then value for the first record will be 0.
Thanks
W
I had similar problems with reseeding “virgin tables” (table that never contained any data). The script below solved my problem
declare @TableName nvarchar(123)
set @TableName = ‘test’
IF NOT EXISTS(select *
FROM SYS.IDENTITY_COLUMNS
JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
WHERE SYS.TABLES.Name = @TableName AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL)
DBCC CHECKIDENT (@TableName, RESEED, 0)
Thanks Dave! Very useful information, keep up the good work.
Hi Pinal,
I use the statement
DBCC CHECKIDENT(mytable, RESEED, 350) in one of my stored procedures.
The table mytable needs to be truncated before I call the above statement, due to stored procedure logic requirements.
However, I see that the execution takes very long time. Sometimes. it takes around 20-30 seconds to complete the execution of this statement. Cant see any reasons.
There are no performance issues with any of the other database objects..in general.
If you can help me answer this problem, its highly appreciated. I follow your blog quite often and I appreciate you for all the work you do for the community.
Hi…
Its help full article, i got my solution from this…
thanx…thanx very much…
Hi Suresh,
About how many rows are in the table before you issue a TRUNCATE TABLE statement?
Secondly, do you have many indexes on this table?
If you have few rows like just a few thousand rows then your server needs a good performance drill down at database level, OS and Hardware levels.
You may find out that the problem lies elsewhere.
Try to maintain your database by checking the integrity of your table first, this might be good for a start.
Under normal circumstances, issuing a TRUNCATE TABLE runs faster than issuing a DELETE statement.
DBCC CHECKIDENT should run instantly.
Regards,
Chris Musasizi
Thank You Dave,
I actually dont knw about how to reset my identity value, i was facing this problem every time when I want to truncate a table which is referenced by another child tables.
It is very helpful to me.
And I want to Thank You on behalf of all my folks (people who work on database) for creating such a beautiful environment to share our knowledge and solve different kinds of problems encounterd.
Hello, I am having the issue that when our staging(demo) database gets updated with live data (which is on another database), the seed is getting overwritten. How can I ensure that the seed value (say of 50000) is maintained?
If you have the SQL Server Management Studio then you can simply open the table in design mode, select the field, go to the Identity Seed property (in the Identity Specification group) and set it to the velue you need. This if you don’t need to change the seed within your application, of course!
Hi, please assist. I have table that looks like the following:
Customer_ID Trans_ID
1000 210
1001 212
1001 254
1001 300
1002 123
1003 155
1003 290
1004 230
1004 267
1004 281
I need to add in an extra column which counts each transaction instance for each Customer_ID. It should look like below:
Customer_ID Trans_ID Trans_number
1000 210 1
1001 212 1
1001 254 2
1001 300 3
1002 123 1
1003 155 1
1003 290 2
1004 230 1
1004 267 2
1004 281 3
thanks in advance.
Select Row_number() over (partition by customer_id order by trans_id asc) as trans_number, * from Table
Hi Jeremy,
Declaring your column as an identity column in this case will not help you to achieve this.
I suggest that you write a good T-SQL to analyse your data & do organise it the way you want it.
Try going through your data using a cursor or any optimised while loop logic and add the extra digit to the data you want.
Regards,
Chris Musasizi
Hi Chris,
thanks for the feedback.
managed to find a built in function that assisted me:
Select Row_Number() Over(Partition By Customer_ID order by Customer_ID, Trans_ID) as Trans_Number
regards
Jeremy
Hi,
I have a table which has an identity column in it. This table is added as an article to an existing Peer to peer replication. I tried reseeding the indentity column on this table to 10000 using the below command
DBCC CHECKIDENT ( ‘AutoProvision.ApprovalRequestInfo_New1′ , RESEED, 10000000)
No errors are reported when I run the above command , but if I go to verify the identity set in this table using the below command
DBCC CHECKIDENT ( ‘AutoProvision.ApprovalRequestInfo_New1′ )
go
I get the following output
Checking identity information: current identity value ‘NULL’, current column value ‘NULL’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I thought this was because the table was added as an article as part of the replication , so I added an additional new table in the DB, with no records ( virgin ) , and then tried to reseed the indentity column on this table to 1000,
I used the DBCC CHECKIDENT to check the indentity , but it still returned me
Checking identity information: current identity value ‘NULL’, current column value ‘NULL’.
Any idea , why the table is not being reseeded ?
Thanks
I have MS SQL 2005 DB with size of 120GB text content.
Need to delete old record by date and it takes for ever to delete and at it use transaction logs that end me out of storage on the server and delete process stop.
how can i bulk delete without sending records to transaction log? and doesnt wait so long.
is this script a right solution, because i dont want to lose the id key:
DELETE from “table_name”
(call_start between ’08/01/2010 12:00:00 AM’ AND ’09/30/2010 11:59:59 PM’)
DBCC CHECKIDENT(“table_name”, RESEED, 0)
Try this
set rowcount 10000
DELETE from “table_name”
(call_start between ’08/01/2010 12:00:00 AM’ AND ’09/30/2010 11:59:59 PM’)
while @@rowcount>0
DELETE from “table_name”
(call_start between ’08/01/2010 12:00:00 AM’ AND ’09/30/2010 11:59:59 PM’)
madhivanan,
your solution didnt work, i edited to:
set rowcount 10000
Delete from SMC_LONGDISTANCE_CDRS
where CALL_START_UTC between 12/25/2008 AND 01/30/2009
while @@rowcount>0
DELETE from SMC_LONGDISTANCE_CDRS
where CALL_START_UTC between 12/25/2008 AND 01/30/2009
and result was :
(0 row(s) affected)
Thanks for your help. problem fixed on test server but i changed it to:
set rowcount 100000
Delete from “table_name”
where CALL_START_UTC between ’2008-12-25 00:00:00′ AND ’2008-12-31 23:59:59′
while @@rowcount>0
Delete from “table_name”
where CALL_START_UTC between ’2008-12-25 00:00:00′ AND ’2008-12-31 23:59:59′
SET ROWCOUNT 0
I have one column Gender in Table. The Data of table is in the form of ‘M’ & ‘F’.
Now i want to replace this data means i want to set ‘F’ where ‘M’ & viceversa.
So how can i write query for this.
Please help me.
update table
set gender=case when gender=’M’ then ‘F’ else ‘M’ end
Just ran into the same problem mentioned by Ron Moses above. It seems the reseed issues affect newly created tables with IDENTITY(1,1) and no prior records, populated via scripts. When I ran a development query to purge data with reseed = 0, then inserted records via scripts, I got a starting identity value of 0. As an ugly workaround, I might use table variables with their own identity keys, and use them to populate the working tables using IDENTITY_INSERT ON. (But only because this is a relatively small SQL DB, and not too many records are involved.) This occurred for me in SQL 2005.
BTW, thanks so much for this blog, it has saved me headaches time and time again.
if have a data in table then i want to reset identity a column(which idendity record like as 1,5,6,7) (i want as 1,2,3,4)
Hi Brijesh,
Go into design mode of that table and select that field. You will get the field properties below. Go to “Identity Specification” option, set (Is Identity) as yes, set Identity Increment as 1, set Identity seed as 1.
After this execute the below command.
DBCC CHECKIDENT(‘Your Table Name’, RESEED, 0)
Thanks,
Yash Thakkar
Cannot truncate table ‘ TABLE_NAME ‘ because it is being referenced by a FOREIGN KEY constraint.
I have been dealing with the issues around DBCC CHECKIDENT and i think I have found the issue and the solution. I’ve been trying to create an environment where I can refresh the database after doing some development leaving all tables empty. I tried Ron Moses solution but still found some tables would add 1 to the reseed values while others would begin with the next higher value. It turns ou that this is related to whether we are truncating or deleting records due to the presence of a foreign key. I found that the following code solves the problem. Please note that I am refreshing tables in all schemas except dbo.
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
– USE DELETE on Tables with foreign keys and Truncate on others
EXEC sp_MSForEachTable ‘declare @schema varchar(255);
SET @schema = PARSENAME(“?”,2) IF @schema ”dbo”
BEGIN
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
BEGIN
DELETE FROM ?
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED,0) WITH NO_INFOMSGS
END
else
BEGIN
TRUNCATE TABLE ?
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED,1) WITH NO_INFOMSGS
END
END;
‘
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
The != appears to be missing in the code posted
SET @schema = PARSENAME(“?”,2) IF @schema ”dbo”
IF @schema != ”dbo”
DBCC CHECKIDENT(‘schema.TableName’,reseed,34)
Thank u Dave… Please tell me how insert DateDiff values to a table
Date to be compared retrieve from another table..
I mean Start Date taken from tb1, End Date taken from tb2, values inserted to tb2
Thanks a lot.
Greetings from México.
hi
Hi Pinal,
There are 100s of records in a table having identy columns out that 15 records has been deleted. My question is that how to find that what are the identities which has been deleted.
You need to join with the numbers table to find this
select n.number from numbers as n left join your_table as t on n.number=t.id_col
where t.id_col is null
Love your blog Pinal Dave, I use it a lot.
Is it worth mentioning that if the table has no rows then the first row will have an identity of the seed value, not seed + 1?
[...] DBCC RESEED Table Identity Value – Reset Table Identity My early career blog discusses about Table Identity Value and how to reset them to the original value. [...]