IDENTITY Value in SQL or Usage of IDENTITY Value

Definition:

Identity values are automatically generated unique numeric values for each row in an Identity column when a new record is added.

create table tablename (column_name [Data_type] identity(seed,increment))

Data_type:Is the data type of the identity column

Seed: 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.

column_name:Is the name of the column that is to be inserted into the new table.

Different ways to get IDENTITY values

1. Global variable @@IDENTITY

Returns the last identity value generated in any table in the current session.

Syntax:

select @@identity

2. SCOPE_IDENTITY() Function

Returns: last identity value generated in any table only within the current scope.

Syntax:

select Scope_identity()

3. IDENT_CURRENT

Is not limited by scope and session; it is limited to a specified table. IDENTCURRENT returns the identity value generated for a specific table in any session and any scope.

Syntax:

select ident_current('tablename')

4. Pseudo Column $Identty :

Syntax:

select $identity from 'tablename'

Returns all the inserted identity values.

select max($identity) from testident

Return the maximun identity value from the table

select min($identity) from testident

Return the minimum identity value from the table

Different ways to set IDENTITY values

create table tablename (id int identity(1,1),Rollno varchar(10))

It will increment the value like this 1,2,3,4,5

create table tablename (id int identity(5,1),Rollno varchar(10))

It will increment the value like this 5,6,7,8,9

create table tablename (id int identity(1,-1),Rollno varchar(10))

It will increment the value like this 1,0,-1,-2,-3,-4

create table testident (id int identity(1,3),Rollno varchar(10))

It will increment the value like this 1,4,7

I hope you learn something useful……