LATEST NEWS
 
How to reset the identity seed value of a table?
Handling identity columns in SQL Server can become tricky particularly when the database is in the maintenance mode. This article explains how to manage the seed value of the identity columns.
By Uma Jebahar      Posted On: Sunday, May 04, 2008
Total Views: 54

Category: SQL Server
Applies to: 324, 234

Identity columns are widely used while designing databases for applications. A typical table creation using an Identity Column as the primary key may look like:


    CREATE TABLE [dbo].[Employees](
        [EmployeeId] [bigint] IDENTITY(1,1) NOT NULL,
        [EmployeeName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
    (
        [EmployeeId] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

As you can see in the code snippet, the identity column is defined using the IDENTITY keyword with two parameters - the seed and increment. In our SQL statement, the seed and increment values are set to 1.

When the first row gets inserted into the table Employees, the EmployeeId column will automatically have the seed value, that is 1. For every row that gets inserted further into the table, the value in the EmployeeId column gets incremented by 1.

To insert records into the table, use the following insert query:

    insert into Employees (EmployeeName) values ('Allen')

To know the current identity value that is used in a table, use the following statement:

    select IDENT_CURRENT('Employees')

This statement will retrieve the last inserted identity value in the Employees table.

Since the insert statement cannot directly insert a value into an identity column, a reset of the value of identity column seed is not possible through normal DML statements.

After inserting few records into the table, if the user wants the identity column value to start from a user-desired higher value, it is nearly impossible through DML statements.

One way to reset the identity column value is to truncate the table. For example,

    TRUNCATE table Employees 

will truncate all content of the table and also reset the identity column present in it.

However, it is possible to reseed the identity column value with a new value with the Database Consistency Checker (DBCC) tool.

DBCC CHECKIDENT is used for repairing identity values.

Syntax:

    DBCC CHECKIDENT 
    ( 
    'table_name'
        [ , {
        NORESEED | { RESEED [ , new_reseed_value ] }
            }
        ]
    )
    [ WITH NO_INFOMSGS ]

Arguments:

'table_name'

Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must comply with the rules for identifiers.

NORESEED

Specifies that the current identity value should not be changed.

RESEED

Specifies that the current identity value should be changed.

new_reseed_value

Is the new seed value to use in reseeding the identity column. When RESEED option is used, the next value that will be inserted into the column will be new_reseed_value + 1. For instance, if the value passed is 1000, the next row inserted will contain 1001. An exceptional case is when a table is in its truncated state, the value passed will be inserted for the next row.

WITH NO_INFOMSGS

Suppresses all informational messages. Upon normal execution, the DBCC CHECKIDENT method returns a message similar to the following informational message:

Checking identity information: current identity value '2', current column value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The WITH NO_INFOMSGS option suppresses the printing of this message.

A sample usage with our Employees table would be:

    DBCC CHECKIDENT('Employees', RESEED, 1000)