Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL : How to reset identity seed after deletion of records in SQL server ?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 582
    Comment on it

    By using the seed value for every new record Microsoft SQL Server's identity column generates sequential values. In this post we will learn how to reseed an identity column.

    Following is the command to reset the identity property :

    DBCC CHECKIDENT (tablename, RESEED, reseed_value)
    

    For example if we want to reset column identity to 10 for table Employee we will run the following command:

    DBCC CHECKIDENT (Employee, RESEED, 10)
    

    In case we want to delete rows from Employee table and restore the value to the previous value, we need to run the below query:

    DELETE
    FROM Employee
    WHERE Employee_id >= 10 ;
    
    DECLARE @Seed NUMERIC(10)
    SELECT @Seed = MAX(Employee_id) FROM Employee ;
    
    DBCC CHECKIDENT (Employee, RESEED, @Seed)
    

    While using the above approach we should be careful not to set a new seed value below existing values as it will generate an error if column is a primary key.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: