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

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)

About these ads

75 thoughts on “SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

    • 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

      Like

      • 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.

        Like

  1. 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)

    Like

  2. 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.

    Like

  3. 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

    Like

  4. Pingback: SQL SERVER - Time Out Due to Executing DELETE on Large RecordSet Journey to SQL Authority with Pinal Dave

    • pinal got it just

      Earlier it is : DBCC CHECKIDENT (schools.tblCaste,reseed,0)

      Success: DBCC CHECKIDENT (‘schools.tblCaste’,reseed,0)

      Like

  5. Hi mithilesh kumar,
    Identity columns does not stand for uniqueness,
    Unless you don’t define identity column as part of Primary Key.

    Like

  6. 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?

    Like

    • 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

      Like

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

    Like

  8. 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!!!

    Like

  9. 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<

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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.

    Like

  13. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  14. 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

    Like

  15. 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)

    Like

  16. 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

    Like

  17. 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

    Like

  18. 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)

    Like

  19. 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.

    Like

  20. 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

    Like

  21. 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.

    Like

  22. 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?

    Like

  23. 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!

    Like

  24. 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.

    Like

  25. 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

    Like

    • 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

      Like

  26. 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

    Like

  27. 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)

    Like

    • 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’)

      Like

  28. 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)

    Like

    • 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

      Like

  29. 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.

    Like

  30. 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.

    Like

    • 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

      Like

  31. 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’

    Like

  32. 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

    Like

  33. 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.

    Like

  34. 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?

    Like

  35. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  36. Hello. This blog is helping me in any manner it can. Am glad using this blog.
    I have a question.
    Scenario:
    I have a table ‘Table1′ with 2 columns namely ‘ID’ & ‘Name’. Primary key on column ‘ID’.
    I have 10 records with ids ranging from 1-10.
    Suppose I have deleted rows whose ids are 2,5.
    I want to refresh my table ‘Table1′ in such a manner that rows whose ids are 3 & 6 should take the place of 2 & 5 respectively and so on.
    Is this possible.

    Thanks in advance.

    Like

    • Hi, Anas
      follow steps given below

      (1) remove identity from column ID. (set identity = no)
      (2) add ID2 column with identity yes.
      (3) update table set ID = ID2;
      (4) remove ID2 column
      (5) set Identity on column ID

      Like

  37. I know this an older article but I’m hoping someone can still assist me.

    I have been ‘gifted’ an archive table where someone has started a new seed instead of using the original seed from when the record was in the production table.

    I want to reseed 7.8 million records so I can match the last archive record up to the first production record, then turn the seed off in the archive table. My process going forward would then be just moving the record with its original seed to the archive table.

    My question is, how can I update the archive seed without blowing up my transaction logs?

    Thanks for any suggestions you might have.

    Like

  38. i’ve tried all the options that all u people specified,but still it showing error
    DBCC CHECKINDENT(tblItem, RESEED,0)

    Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

    Like

    • Hello Herby,
      It’s CHECKIDENT , not CHECKINDENT as you say, hence the error.
      There’s no such command as CHECKINDENT on MS-SQL.

      Chris.

      Like

  39. Hello Herby,
    It’s CHECKIDENT , not CHECKINDENT as you say, hence the error.
    There’s no such command as CHECKINDENT on MS-SQL.

    Chris.

    Like

  40. Please follow this code for specific database and schema .

    DBCC CHECKIDENT(‘[Database Name].[schema ].[Table Name]’, RESEED, 0)

    Like

  41. Hi pinal,

    CREATE TABLE dbo.Test(id INT IDENTITY(1,1),Num INT)
    GO
    INSERT INTO dbo.Test VALUES(1),(2)
    GO
    DELETE FROM dbo.Test
    GO
    DBCC CHECKIDENT (‘dbo.Test’,RESEED,10)
    GO
    INSERT INTO dbo.Test VALUES(1)
    GO
    SELECT MAX(id) FROM dbo.Test
    GO
    DROP TABLE dbo.Test
    GO
    CREATE TABLE dbo.Test(id INT IDENTITY(1,1),Num INT)
    GO
    DELETE FROM dbo.Test
    GO
    DBCC CHECKIDENT (‘dbo.Test’,RESEED,10)
    GO
    INSERT INTO dbo.Test VALUES(1)
    GO
    SELECT MAX(id) FROM dbo.Test
    GO

    did you try to predict the output, the result will astonish beware of using Dbcc Checkident

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s