Identity Column:

Topics:

• Identity
  
• SET Identity_Insert <Table Name> ON|OFF

• SCOPE_IDENTITY(function)
• IDENT_CURRENT(function)
• @@IDENTITY
• IDENT_INCR (function)
• IDENT_SEED (function)

Identity

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

SET Identity_Insert

syntax:

SET Identity_Insert <Table Name> ON|OFF

In a session only one table can have the Identity_Insert set to ON. If we try to ON the Identity_Insert on one table when there is another table for which Identity_Insert is ON, then the Server throws an error like this...

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'table1'. Cannot perform SET operation for table 'table2'.

SCOPE_IDENTITY

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Syntax: SCOPE_IDENTITY( )

IDENT_CURRENT

Returns the last identity value generated for a specified table in any session and any scope.

Syntax: IDENT_CURRENT('table_name')

@@IDENTITY

Returns the last-inserted identity value (of any table).

Syntax: @@IDENTITY

SCOPE_IDENTITY vs IDENT_CURRENT vs @@IDENTITY

IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

	Table	Session	Scope
IDENT_CURRENT	Specific	Any	Any
@@IDENTITY	Any	Current	Any
SCOPE_IDENTITY	Any	Current	Current

IDENT_INCR & IDENT_SEED function

IDENT_INCR function: Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.

syntax: IDENT_INCR ( 'table_or_view' )

IDENT_SEED function:Returns the seed value that was specified when an identity column in a table or a view that has an identity column was created.

syntax: IDENT_SEED ( 'table_or_view' )

Scenario Questions: (What is the output of this query)

Query 1:

create table #t1(sno int identity(1,1), name char(1))

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

dbcc checkident (#t1, RESEED, 10)

insert into #t1 (name) values ('a')

select max(sno) from #t1

Ans: 11

Query 2:

create table #t1(sno int identity(1,1), name char(1))

dbcc checkident (#t1, RESEED, 10)

insert into #t1 (name) values ('a')

select max(sno) from #t1

Ans: 10

Query 3:

create table #t1(sno int identity(1,1), name char(1))

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

dbcc checkident (#t1, RESEED, 2)

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

select * from #t1

Ans: the values in 'sno' column are: 1,2,3,4,5,3,4. Note 3,4 are repeated.

Query 4:

create table #t1(sno int identity(1,1) PRIMARY KEY, name char(1))

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

insert into #t1 (name) values ('a')

dbcc checkident (#t1, RESEED, 2)

insert into #t1 (name) values ('a')

select * from #t1

Ans: Since a PRIMARY KEY (or UNIQUE) constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

Questions:

1) Will IDENTITY column on a table enforce uniqueness of that column?

Ans 1) NO.

Explanation: see Query 3 above.