vijay

welcome Netizen

welcome Netizen
Hi folks this site Share Basic Knowledge in .NET, DotNet, C#,DOT NET CODE, ASP.NET, VB.NET, DOT NET TRICKS,Dotnetcode,Android and Latest Technology Articles in VB,Android and ASP.NET

Saturday, January 7, 2012

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……


0 comments:

Post a Comment