Identity

We can apply identity property on column, whose value increases automatically.
No need to pass this column value while inserting data in the table.

Identity(seed value, increment value)

Seed -  Starting value of a column.

Increment - Previous value + plus increment value.

How to Create Identity on table / Column -

1) Using Query 

Create Table Student

(

Id Int Identity(1,1),

Name varchar(25)

)

2) Using Wizard - Designer view.

Characteristics of Identity - 

1) Only one per table.

Alter Table Student

Add EnrolmentNumber Int Identity(1,1) NOT NULL 

Msg 2744, Level 16, State 2, Line 9

Multiple identity columns specified for table 'Student'. Only one identity column per table is allowed.

2.1) Add Identity After Table Creation  for New column

Create Table Student

(

Id Int,

Name varchar(25)

)

Alter Table Student

Add EnrolmentNumber Int Identity(1,1) NOT NULL 

output 

Possible.

2.2) Add Identity After Table Creation for Existing Column

Create Table Student

(

Id Int,

Name varchar(25)

)

Alter Table Student

Alter Column Id Int Identity(1,1) 

Msg 156, Level 15, State 1, Line 17

Incorrect syntax near the keyword 'Identity'.

for Existing Column we can't create identity after table creation.

3) How to insert data into identity column explicitly.

Create Table Student

(

Id Int Identity(1,1),

Name varchar(25)

)

Insert Into Student(Name) values ('Shyam')

Select * from Student

Set identity_Insert Student ON 

Insert Into Student(Id,Name) values (3,'Anil')

Set identity_Insert Student OFF 

Output 

Id Name

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

1 Shyam

3 Anil

we inserted 3 in above example, Now identity is 4.

Insert Into Student(Name) values ('Shubham')

Id Name

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

1 Shyam

3 Anil

4 Shubham

Set identity_Insert Student ON 

Insert Into Student(Id,Name) values (2,'Sunil')

Set identity_Insert Student OFF 

Id Name

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

1 Shyam

3 Anil

4 Shubham

2 Sunil

Insert Into Student(Name) values ('Kumar')

Id Name

1 Shyam

3 Anil

4 Shubham

2 Sunil

5 Kumar

Inserted 5th id because Identity value 5.

if i insert 100 as then next record will be 101. it take max value of that column.

Set identity_Insert Student ON 

Insert Into Student(Id,Name) values (100,'Gopal')

Set identity_Insert Student OFF 

Insert Into Student(Name) values ('mehak')

Id Name

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

1 Shyam

3 Anil

4 Shubham

2 Sunil

5 Kumar

100 Gopal

101 mehak

--Conclusion:

if we manually insert idenity value less than current_identity value then no change in identity. 

if we manually insert idenity value greater than current_identity value then our inserted value+1 is the identity value.

4) DEFAULT or NULL are not allowed as explicit identity values.

Set identity_Insert Student ON 

Insert Into Student(Id,Name) values (NULL,'Pruthvi')

Set identity_Insert Student ON 

Msg 339, Level 16, State 1, Line 59

DEFAULT or NULL are not allowed as explicit identity values.

5) Identity can not Update

Update Student Set ID=10 where Id=1

--Msg 8102, Level 16, State 1, Line 36

--Cannot update identity column 'Id'.

6) Allow Duplicate Value

Create Table Student

(

Id Int Identity(1,1),

Name varchar(25)

)

Insert Into Student(Name) values ('Shyam')

Select * from Student

Set identity_Insert Student ON 

Insert Into Student(Id,Name) values (3,'Anil')

Set identity_Insert Student OFF 

Set identity_Insert Student ON 

Insert Into Student(Id,Name) values (3,'Anil')

Set identity_Insert Student OFF 

Output

Id Name

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

1 Shyam

3 Anil

3 Anil

7) Disable Identity 

From Wizard we can disable.

8) Check Identity Value- 

Select IDENT_CURRENT('Student') AS CurrentIdentityValue 

CurrentIdentityValue

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

101

DBCC CHECKIDENT ('Student', NORESEED)

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

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SELECT

IDENT_SEED('Student') AS Seed,

IDENT_INCR('Student') AS Increment,

IDENT_CURRENT('Student') AS CurrentIdentity

SELECT

MAX(IDENTITYCOL) AS MaximumIdentity,

MIN(IDENTITYCOL) AS MinimumIdentity

FROM Student

9) Reset Identity Value

Delete From Student

Select IDENT_CURRENT('Student') AS CurrentIdentityValue 

CurrentIdentityValue 

---------

101

DBCC Checkident('Student',reseed,0)

Checking identity information: current identity value '1'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Insert Into Student(Name) values ('mehak') --will be 1st Id 

10) Can we Change Seed and Increment Value,

1)change seed value

Using Wizard

DBCC CHECKIDENT ('Student', RESEED, 5)

----5 Means from this value you can insert. 

----if No Primary key then we can easily insert

2)change Increment value

Using Wizard we can change this value.

Scope Identity  and @@Identity

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. 

SCOPE_IDENTITY returns values inserted only within the current scope

@@IDENTITY is not limited to a specific scope.

Drop table IdenetityExample

Create Table IdentityExample

(

Id Int primary key identity(1,1),

Name varchar(25)

)

Insert Into IdentityExample values ('A')

Insert Into IdentityExample values ('B')

Insert Into IdentityExample values ('C')

Insert Into IdentityExample values ('D')

Insert Into IdentityExample values ('E')

Insert Into IdentityExample values ('F')

Select * from IdentityExample

Select IDENT_CURRENT('IdentityExample')--6

Select SCOPE_IDENTITY() --6

Select @@IDENTITY--6

Create Procedure SPIdentityExample

As

Begin

Insert Into IdentityExample values ('ZZ')

END

Exec SPIdentityExample

Select @@IDENTITY   ---glbal level  --7

Select SCOPE_IDENTITY()---Local level---6

--                      Session     Scope

--@@IDENTITY         Same Session Global scope value

--SCOPE_IDENTITY() Same Session Local scope value