Sql Server Identity and DBCC CHECKIDENT

Sql Server Identity and DBCC CHECKIDENT

Identity are basically use generate autonumber for a columns in sql server table.

seed : The first row value that is inserted into table.

increment: incremental value that is added to identity value of previous record. 

Suppose we needed to set start value is 100 with increment of 5 i.e next row value will be 105

then syntax will be.

Default Identity always be (1,1)

Facts about Identity:

  • The identity column does not guarantee of the uniqueness of the record. for uniqueness its should either be unique Key or Primary key.
  • Consecutive values within a transaction not guaranteed to get consecutive values for the rows.
  • Consecutive values not guaranteed for next row after server restart or failover.
  • Only one identity column per table is allowed.

DBCC CHECKIDENT: To Check the current identity value of specific table use DBCC CHECKIDENT,You may also use DBCC CHECKIDENT to set manually a new current identity value for the identity column.