Tuesday 19 December 2017

IDENT_CURRENT SQL Server



IDENT_CURRENT return last inserted identity field value from any session any scope table.
One most interesting things which surprise me while I had used this first time. Which is follows

You can get SQL server generated identity value which inserting row, means it is not necessary that identity value only can get after save a row. Not at all!

You can use this feature while creating some userid and roll number type field in many applications.

i.e: 10000001,2000829 etc.

CREATE TABLE dbo.stu( 
   ID INT IDENTITY NOT NULL PRIMARY KEY,  
   Roll varchar(20),
   Name VARCHAR(40) NOT NULL 
   City VARCHAR(40) NOT
NULL 

); 



insert into stu values('10000' + cast(IDENT_CURRENT('emp') as char), 'Your Name','Your city')

e.g

insert into stu values('10000' + cast(IDENT_CURRENT('emp') as char), 'Sona','VNS')
insert into stu values('10000' + cast(IDENT_CURRENT('emp') as char), 'Mona','ALLD')
insert into stu values('10000' + cast(IDENT_CURRENT('emp') as char), 'Sita','DLH')

After inserting some row see table data

select * from stu;




No comments:

Post a Comment