aspx Tutorial

NET Articles,jQuery demo, asp.net with jQuery, online tutorial,Jquery, SilverLight, Javascript, asp.net,JSON, MVC,.NET Articles,demo, Web Services,
Advertise Here

Toolbar

Get our toolbar!

Advertize



Posted by Aamir Hasan   on Tuesday, June 22, 2010 Total Views:  

SCOPE_IDENTITY, IDENT_CURRENT,@@IDENTITY functions return last-generated identity values.


IDENT_CURRENT()

Returns the last identity value generated for a given table in any session and any scope  is called SCOPE_IDENTITY. When table have no identity column it returns null.


SCOPE_IDENTITY()

Returns the last identity value generated for any table in the current session and the current scope  is called SCOPE_IDENTITY


@@IDENTITY()

Returns the last identity value generated for any table in the current session, across all scopes is called @@IDENTITY. @@IDENTITY is limited to cureent session, but not limited to current scope.

 

EXAMPLE

DECLARE @TABLE TABLE
    (
      ID INT IDENTITY ,
      FULLNAME NVARCHAR(MAX) ,
      GENDER CHAR(1) DEFAULT NULL ,
      CREATEDDATE DATE ,
      CREATEDTIME TIME
    )

INSERT  INTO @TABLE
VALUES  ( 'AAMIR HASAN', 'M', GETDATE(), GETDATE() )
INSERT  INTO @TABLE
VALUES  ( 'AHMED HASAN', 'M', GETDATE(), GETDATE() )
INSERT  INTO @TABLE
VALUES  ( 'HINA ', 'F', GETDATE(), GETDATE() )
INSERT  INTO @TABLE
VALUES  ( 'SANA', 'M', GETDATE(), GETDATE() )
INSERT  INTO @TABLE
VALUES  ( 'AAMIR HASAN', 'M', GETDATE(), GETDATE() )
SELECT  *
FROM    @TABLE
SELECT  @@IDENTITY ,
        SCOPE_IDENTITY() ,
        SCOPE_IDENTITY()

 

In Above example, this will always give you a correct result as shown in the Above Figure. @@IDENTITY can be wrong, if table has a Trigger,becasue @@IDENTITY return last value of identity column in a current session. As above i have defined the defination SCOPE_IDENTITY returns last identity value in a current session and current scope. And IDENT_CURRENT return a last identity value in any scope and any session. IDENT_CURRENT can return a last identity  inserted value of any other session. So it is aproved that in different case we use SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT.However, normally you would use the scope_identity() function.

Download

USER_INFORMATION_TABLE_SQL.sql (645.00 bytes)

Tags:
Category: All
Protected by Copyscape Online Plagiarism Tool

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Advertizement 1
Advertizement 2
Advertizement 3
Advertizement 4
Advertizement 5