Identity property

Identity

We can define a column with identity property in a table.

Its auto increment number and has two arguments

IDENTITY [ ( seed , increment ) ]

seed

Is the value that is used for the very first row loaded into the table.

increment

Is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Example

seed 5 increment 3

first row will have value of 5

next one will have value of 8

next one will have value of 11

*If a column is defined as identity it can not be altered

*Its not providing guarantee of uniqueness

You have to create either primary key or unique key

*If values are deleted than there will be gap in values generated

*If multiple rows are inserted at same time there is no guarantee for consecutive numbers

*If insert operation fails due to any reason the identity value will not be reused.

*To insert  values manually use SET IDENTITY_INSERT ON

*To reset value of identity column

DBCC Checkident

*To get value of identity column

       1.@@identity

       2.Scope_identity()

       3.ident_current

Since 2012 we have option of sequence is available.

Sequence is independent of identity , is more flexible compare to identity.

CREATE TABLE TEST

  (

     id    INT IDENTITY (1, 1),

     value VARCHAR(100)

  )

INSERT INTO TEST

VALUES     ('A')

INSERT INTO TEST

VALUES     ('B')

INSERT INTO TEST

VALUES     ('C')

SELECT *

FROM   TEST

*Sometimes we have only one identity column in a table

Lets see how to insert value here

CREATE TABLE TEST_1

  (

     id INT IDENTITY(1, 1)

  )

INSERT INTO TEST_1

DEFAULT VALUES

INSERT INTO TEST_1

DEFAULT VALUES

SELECT *

FROM   TEST_1