Friday, January 6, 2012

Identity in sql server

Identity is used to specify to automatically generate values for a column during insert.
IDENTITY [( seed , increment )]
seed is used to specify the starting value for the identity and increment is used ti specify how much increment to be done to generate a new value for the new row.The fallowing example creates a table with name Customers in which Identity is provided on Cid column
Create table Customers(Cid int PRIMARY KEY IDENTITY(1000,1),cname varchar(30),city varchar(20)
when identity was specified on a column then it is not possible to provide a value for that column manual and during insert you must exclude that column .The fallowing example insert the rows in to customer table
Insert customers(cname,city) values('AB','Hyderabad')
Insert customers(cname,city) values('Bc','New York')
Getting identity values:
To get the details of an identity like seed of the identity increment of the identity and current value of the identity use the fallowing set of functions
IDENT_SEED('customers ')
//returns the seed of the identity available on customers table 
IDENT_INCR('customers ')
//returns increment of the identity available on the customers  table
IDENT_CURRENT('customers ')
//returns current of the identity available on the customers  table
Post a Comment