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
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
Truly gud idea thanks for explore i used truncate instead of reseed and its works fine fro me..
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 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