Thursday, 20 November 2014

@@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT







Open Microsoft Sql server management studio

Create following two tables and trigger





Now we will execute following commands but within same session (with in same query window)

Result of both select statements is empty. 


Now we will execute following commands but within same session (with in same query window)


Note: Insert statement on table1 will insert value ‘1' in table 1 and trigger will insert value ‘100' in table2


So we have two insert on single insert


One in table1 and another in table2 so we have two scope one is current related to table1 one another is global scope related two table1 and table2


Now open a new query window (new session) and execute the following commands:







 So we have two scenario to compare session and scope
SessionScope
@@IDENTITYSame SessionGlobal scope value
SCOPE_IDENTITY()Same SessionLocal scope value
IDENT_CURRENT()May be differentDepends on table name passed in parameter
Conclusion:
SELECT @@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes(i.e. global scope).

SELECT IDENT_CURRENT : returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).

SELECT SCOPE_IDENTITY(): returns the last identity value generated for a specific table in any session and any scope(i.e. global scope).


0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (9) Web Service (1) XMl (1)