Identity Functions

Over several years of working with RDBMS products like MS SQL Server (since 2000) and Oracle 11g, there are times when really simple concepts are so darn confusing for whatever reason.  I imagine that it really depends on the person about what they find confusing but for me the proper use of @@IDENTITY and SCOPE_IDENTITY() and IDENT_CURRENT() always causes me pause. 

My work as a .NET developer and DBA and Data Warehouse Architect has required extensive use of this code and it seems that regardless of the type of database work you do it is very important to have a solid grip on what the differences really are.

There are many advantages to being certified in multiple version of SQL Server but what I consider one of the disadvantages of studying towards multiple certifications on different versions is having to review some of the more simple concepts over and over.  For example, an INT data type was the same in 2008 when I started studying towards my MCTS in SQL Server 2005 and it is the same today.   

Luckily studying some of the same concepts repeatedly really serves as a refresher.  This is one of those things that no matter how many times I go over it I can’t help but enjoy confirming the difference between the three pieces of code.

It really boils down to scope being the main difference.  I’ll assume we all know what an IDENTITY is.  It is just a property of a table column that is very helpful in creating key values in an easy and predictable way.  

When we insert into a table that contains a column that has a primary key that is defined as an Identity then we don’t need to tell it what value to be.  The identity property does it for us.

Now when we are coding sql stored procs or functions there are times when we need to know more information about the identity values that are used when inserting rows into tables.  That is where @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() come in handy.

IDENT_CURRENT()

The one that I find easies to remember is IDENT_CURRENT() function.  If we pass it a the name of a table such as IDENT_CURRENT(‘Sales.Product’) then it will tell us the last identity value that was given to that table.

Now this is where it starts to get a bit more confusing J.

@@IDENTITY

This one returns the most recent identity value given to a database engine connection and it doesn’t care about the table or even the scope.  

SCOPE_IDENTITY()

This one returns the most recent identity value given to a database engine connection but it does care about the scope of the statement but still doesn’t care about the table.

Still confused?

Let’s look at some simple code to help make it more clear.

Ill first define a table called IdentitySample with an identity column called ID.

CREATE TABLE dbo.IdentitySample

(

Id INT NOT NULL IDENTITY(1, 1)

CONSTRAINT PK_ID PRIMARY KEY,

ProductId INT NOT NULL,

SampleDate DATE NOT NULL

);

If I insert some sample data into a sample table as such.

INSERT INTO dbo.IdentitySample(ProductId, SampleDate) VALUES

(1, '20150502'),

(1, '20150603'),

(2, '20150708');

Then view the data.

Id            ProductId            SampleDate

1              1                              20150502

2              1                              20150603

3              2                              20150708

Now if I call three of the identity columns discussed in the same session that I ran the last insert in I get the following.

SELECT

IDENT_CURRENT(‘dbo.IdentitySample’) as A,

@@IDENTITY as B ,

SCOPE_IDENTITY() as C;

I get the following:

A             B             C

3              3              3

They all have the same value which is the last identity value assigned to the dbo.IndentitySample table.

If I were to open another window and rerun the same statement I would get the following.

A             B             C

NULL     NULL     3

This is because for the scope of this newly opened connection there has been no identity values issued but the last identity issued to the dbo.IdentitySample is still 3.

Now if you were using a stored proc to insert a row and a new identity value was assigned then the SCOPE_IDENTITY() function will return value that that stored proc created.   

Now if the stored proc were to call a different stored proc (out of scope) then @@IDENTITY would return the value of the by that stored proc regardless of the table or the scope of the statement that created the identity.

Thanks for reading, 

SQL Canuck

You can read more about this at 

https://msdn.microsoft.com/en-CA/library/ms187342.aspx