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











No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts