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
- SCOPE_IDENTITY : This function returns the last identity value generated for any table in the current session and the current scope.
- @@IDENTITY:This function returns the last identity value generated for any table in the current session, across all scopes.
- 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
CREATE TABLE [DBO].[tblName ]
(
[ID] INT IDENTITY(1,1),
[Name] VARCHAR(100)
)
GO
INSERT INTO [DBO].[tblName ] ([Name]) VALUES (Vishal);
INSERT INTO [DBO].[tblName ] ([Name]) VALUES (Pawar);
Now if we check following query
SELECT @@IDENTITY
GO
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
Copyright © 2011 – 2012 Vishal Pawar
-
August 3, 2011 at 9:10 amBISQL # 36 :Queries to Understand more on Identity aspect in SQL {Identity Part–II} « SQL Server Mentalist
-
August 3, 2011 at 9:39 amBISQL # 37 :Checking, Seeding and Reseeding feature of Identity in SQL {Identity Part–IV} « SQL Server Mentalist
SQL Server Mentalist 