Home > Query, SQL > BISQL # 35 :Deep dive into Identity Functions in SQL {Identity Part–II}

BISQL # 35 :Deep dive into Identity Functions in SQL {Identity Part–II}


Hi Friends,

Continuing from my last post on identity :BISQL # 34 :What is Identity IN SQL {Identity Part–I} « SQL Server Mentalist


Today we are going to discuss about function in related to Identity in SQL Server

Sometimes its necessary or business requirement when we have capture Identity. This we can achieve by following three ways which are nothing but SQL Server Function :SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() functions.

Lets discuss one by one

  1. SCOPE_IDENTITY : This function returns the last identity value generated for any table in the current session and the current scope.
  2. @@IDENTITY:This function returns the last identity value generated for any table in the current session, across all scopes.
  3. IDENT_CURRENT:This function returns the last identity value generated for a specific table in any session and any scope.

The above mention  functions are used to retrieve IDENTITY value for single record inserts.

But Believe it will not support for multiple inserts.So how we are going to achieve that:

Lets say I have tblName table in my Database

[Name] VARCHAR(100)
INSERT INTO [DBO].[tblName ] ([Name]) VALUES (Vishal);
INSERT INTO [DBO].[tblName ] ([Name]) VALUES (Pawar);

Now if we check following query


We have output as 2

Some facts about above function:
  • SELECT @@IDENTITY:- If a trigger was fired for the INSERT, the value of @@IDENTITY might have changed.
  • SELECT SCOPE_IDENTITY:- If an INSERT trigger also inserted a row that contained an identity column, it would be in a different scope.
  • SELECT IDENT_CURRENT(‘Customer’): – To know the last IDENTITY value inserted in a specific TABLE from any application or user.

We will have some more aspect on Identity in upcoming post

For more interesting find on related topic on daily basis you can visit my link resource website :Link Resource # 11 August 1 – August 2 « Dactylonomy of Web Resource

Hope this helps !!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 36 : Queries to Understand more on Identity aspect {Identity Part–III}

· BISQL # 37 :Checking, Seeding and Reseeding of Identity {Identity Part–IV}

· BISQL # 38 : Introduction To Function – {SQL Server Function Part – I }

· BISQL # 39 : Details of Row set functions – {SQL Server Function Part – II}

· BISQL # 40 : SQL Server Code Name “Denali” CTP3 Product Guide


Hope you will like this post on Identity functions in SQL Part II.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

               For More information related to BI World visit my Mentalist Blog

                           Link Resource Blog >> Daily Interesting links

                           SQL Server Mentalist >> SQL Learning Blog

                           Business Intelligence Mentalist >> BI World

                                          Connect With me on

            | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                   Copyright © 2011 – 2012 Vishal Pawar

Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: