Usage of IDENT_CURRENT function in SQL Server

Kishork80
Posted by in Sql Server category on for Advance level | Points: 250 | Views : 25834 red flag
Rating: 5 out of 5  
 1 vote(s)

Usage of IDENT_CURRENT function in SQL Server

Introduction

IDENT_CURRENT function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Let us see the below example for simplicity.

First of all create a table with empid and empname fields. Note that the empid is an identity column with seed 1.


 

 

Now Insert some values (values for 'empid' is not required as this is an identity column ).

insert into dbo.empTable values ('employee 1')

insert into dbo.empTable values ('employee 2')

insert into dbo.empTable values ('employee 3')

insert into dbo.empTable values ('employee 4')

insert into dbo.empTable values ('employee 5')

insert into dbo.empTable values ('employee 6')

just to confirm run the below query:

select * from dbo.empTable

output:

1 employee 1
2 employee 2
3 employee 3
4 employee 4
5 employee 5
6 employee 6


Now run the below query:

select IDENT_CURRENT( 'empTable' )

NOTE: The above function accepts table / view name.

Output:

6

Now insert one more row and then check this:

insert into dbo.empTable values ('employee 7')

select IDENT_CURRENT( 'empTable' )

Output:

7.

What does the function do ? This always give you the current 'empid' value from the table.

Now run the query below.

insert into dbo.empTable values ('employee 8')

select IDENT_CURRENT( 'empTable' )

select @@IDENTITY

what is the output :

8

That means @@IDENTITY does the same thing!!!!

Wait and watch:

Open a new Query window and run the below query again:

select IDENT_CURRENT( 'empTable' )

select @@IDENTITY

Output:

8

NULL

Oops Strange ! RIGHT???

The difference between them is

·  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.

One more observation which is very important:

Run the below query in seperate query window:

select MAX(empid) from dbo.empTable

select IDENT_CURRENT( 'empTable' )

delete from dbo.empTable where empid = 8

select MAX(empid) from dbo.empTable

select IDENT_CURRENT( 'empTable' )

What is the output we get?

8

8

7

8

What we see is MAX(empid) always give the max of the row but the

IDENT_CURRENT( 'empTable' ) gives the last inserted value. this is very useeful for transactions table where we have some data retrieving logic between more than one table but having dependency on the identity column.

IDENT_CURRENT( 'tablename/viewname')  decoded.

happy reading.

 

Page copy protected against web site content infringement by Copyscape

About the Author

Kishork80
Full Name: kishor kumar
Member Level: Starter
Member Status: Member
Member Since: 7/1/2010 2:49:48 AM
Country: India
kishor kumar
http://www.dotnetfunda.com
Having 6 pyears of exp in dot net and still counting...

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)