IDENTITY PROPERTY in SQL Server

In this article we will learn about IDENTITY PROPERTY in SQL Server. Sometimes we need a column whose values can uniquely identifying the rows in the table. To achieve this purpose, this column should contains the unique values and it can’t contains the NULL or empty values. We can use this column in the “Where” clause of the Select statement to retrieve the data from the table quickly. Please read my previous article Different Types of SQL JOINs.

We know that an IDENTITY column property creates an IDENTITY column in a table. This property can be used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. We can use an identity property with data types likes:: SMALLINT, TINYINT, INT, BIGINT, NUMERIC and DECIMAL.

The IDENTITY property makes generating unique values easy. IDENTITY isn’t a datatype. It’s a column property.

Example of IDENTITY PROPERTY in SQL Server

CREATE TABLE Employee(
empId smallint IDENTITY NOT NULL,
empName VARCHAR(50) NOT NULL
)

The system function @@IDENTITY contains the last identity value used by the connection.

SELECT @@IDENTITY:– Returns the last identity values that were generated in a table in the current session. This method is not limited to a specific scope.

SELECT SCOPE_IDENTITY:– Returns the last identity values that are generated in any table in the current session. This method returns values inserted only within the current scope.

SELECT IDENT_CURRENT(‘Employee’): – Returns the last identity value generated for a specific table in any session and any scope. This method is not affected by scope and session, it only depends on a specific table.

Differences between @@IDENTITY , SCOPE_IDENTITY , IDENT_CURRENT

All these three methods are used to retrieve the last identity value generated for a specific table, but each method has some minor differences as in the following: 

@@IDENTITY method is not limited to a specific scope.

SCOPE_IDENTITY method is limited for current scope.

IDENT_CURRENT method is not affected by scope and session, it only depends on a specific table.

Hope It will clear about Identity Property in SQL Server.