Difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() in SQL SERVER

Let’s first understand, What is identity column in SQL Server?

When we create a column in SQL table by setting indentity value of that column then it is called Identity Column also know as
Auto Increment column.

Syntax to create identity column id

CREATE TABLE [database_name].[owner].table_name
(column_name data_type [length] [IDENTITY(seed, increment)] [NOT NULL])

Where
seed: Starting value for the identity column
increment: The increment value for the identity column when next record inserted on table.

When you insert the data in a table contains identity column you need not to provide value for that column. SQL server takes care of that.

Note*: If you forcefully want to insert the value for the identity column then you need to enable the identity insert on that table using
the below syntax.

SET IDENTITY_INSERT [ DATABASE. [ owner. ] ] { table } { ON | OFF }

and once you done with insert make the value for identity to off for that column.

Sometimes, there is a situation when we need to force the identity column value to start from a specified value, this you can do using the below SQL statement.

Declare @MaxID int
Select @MaxID = Max(IdentityColumn)+1 From YourTableName
DBCC CHECKIDENT (‘YourTableName’, RESEED, @MaxID);

Now, lets understand how you can get the last indentity value inserted by using @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() in SQL SERVER and how it impact in your database.

SELECT @@IDENTITY() returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value ENTERED into a table in your current session, while it is not limited to the current scope. It will return the last identity value that is either explicitly created (creatd by any trigger or user defined function).

SELECT SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and within the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY returns the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created (not includes the identity created by any trigger or user defined function).

SELECT IDENT_CURRENT(‘tablename’) returns the last IDENTITY value produced in a table. It does not DEPEND on any session or scope, instead it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

 

Advertisements

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 )

Google+ photo

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

Connecting to %s