Identity Columns in Table Object with DBCC Checkident

Identity Columns : When a row is inserted into a table the column will take the value of the current seed incremented by the step.

An identity column is not guaranteed to be unique nor consecutive. You should always place a unique index on an identity column if your system requires uniqueness.

CREATE TABLE table_name

(col1 INT IDENTITY(1,1),

 col2 INT)

How to reset Identity column.

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




DBCC CHECKIDENT (yourtable, reseed, 0)

insert into table_name (col2) values(20)

insert into table_name (col2) values(30)

select * from table_name

col1        col2

----------- -----------

1           20

2           30

(2 row(s) affected)

How to find current identity seed? (using DBCC checkident)

DBCC checkident (table_name)

Checking identity information: current identity value '2', current column value '2'.

Remember the next value will be the current seed plus the step

Post Reference: Vikram Aristocratic Elfin Share