All About IDENTITY Property in SQL Server

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.

We can see that there are various system functions for identity properties like: IDENTITY (), IDENTITYCOL, IDENTITY_INSERT, @@IDENTITY, SCOPE_IDENTITY (), IDENT_SEED and IDENT_INCR.

IDENT_CURRENT

1. IDENTITY (): Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Note: The IDENTITY property is not the same as the SQL-DMO Identity property that exposes the row identity property of a column.

Syntax: IDENTITY [(seed, increment)]

Seed: It's the value that is used for the very first row loaded into the table.

Increment: It's the incremental value that is added to the identity value of the previous row that was loaded.

Example

-- CREATE a test table:
CREATE TABLE Test
(
ID INT IDENTITY (1, 1),
NAME VARCHAR (5)
);
-- INSERT some records:
INSERT INTO Test (NAME)
SELECT 'A'
INSERT INTO Test (NAME)
SELECT 'B'
INSERT INTO Test (NAME)
SELECT 'C'
GO
-- Check inserted records:
SELECT * FROM Test -- 3 records, with ID value 1, 2, 3.
GO
--// Final cleanup
DROP TABLE Test
--Results:

 ID  NAME
----------- -----
1         A
2         B
3         C

2. IDENTITYCOL:
This Keyword automatically refers to the IDENTITY column of the table.

Example1

--Using IDENTITYCOL with WHERE clause and ORDER BY clause:
SELECT * FROM Test
WHERE IDENTITYCOL>1
ORDER BY IDENTITYCOL DESC
--Results: 

ID    NAME
----------- -----
3        C
2        B

Example2

-- Using IDENTITYCOL with COUNT () function instead of the original column name & with Column name separately:
SELECT * FROM Test
SELECT NAME, COUNT (IDENTITYCOL AS Total FROM Test
GROUP BY NAME
--Results:

ID    NAME
----------- -----
1       A
2       B
3       C
4       C

NAME  Total
----- -----------
A         1
B         1
C         2

3. IDENTITY_INSERT: In an INSERT statement we can't provide an IDENTITY column name and value, unless you explicitly SET IDENTITY_INSERT ON.

When we set IDENTITY_INSERT then we can insert duplicate identity values.

Note: SET IDENTITY_INSERT ON does not work with Table Variables.

Example

SET IDENTITY_INSERT Test ON
GO
INSERT INTO Test
(ID, name)
VALUES (5,'D')
GO
SET IDENTITY_INSERT Test OFF
SELECT * FROM Test
|
--Results:

ID     NAME
----------- -----
1        A
2        B
3        C
4        C
5        D
5        D

4. @@IDENTITY: It returns the last identity value used by the current session.
5. IDENT_SEED: It returns the seed value that is the starting number of IDENTITY column, default is 1.
6. IDENT_INCR: It returns the increment value of IDENTITY column, default is 1.
7. IDENT_CURRENT: It accepts table name as parameter and returns the last identity value inserted into that table.
8. SCOPE_IDENTITY (): It returns the last identity value generated for any table in the current session and the current scope.

Example1

INSERT INTO Test (NAME)
SELECT 'E'
GO
SELECT @@IDENTITY '@@IDENTITY'
SELECT SCOPE_IDENTITY () 'SCOPE_IDENTITY ()'
SELECT IDENT_SEED ('Test') 'IDENT_SEED ()'
SELECT IDENT_INCR ('Test') 'IDENT_INCR ()'
SELECT IDENT_CURRENT ('Test') 'IDENT_CURRENT ()'
SELECT * FROM Test
--Results: 

@@IDENTITY
---------------------------------------
6
SCOPE_IDENTITY ()
---------------------------------------
6
IDENT_SEED ()
---------------------------------------
1
IDENT_INCR ()
---------------------------------------
1
IDENT_CURRENT ()
---------------------------------------
6
ID     NAME
----------- -----
1         A
2         B
3        C
4        C
5        D
5        D
6        E

Example2

-- CREATE a test table:
CREATE TABLE Test
(
ID INT IDENTITY (2, 3),
NAME VARCHAR (5)
);
-- INSERT some records:
INSERT INTO Test (NAME)
SELECT 'A'
INSERT INTO Test (NAME)
SELECT 'B'
INSERT INTO Test (NAME)
SELECT 'C'
GO
SELECT
@@IDENTITY '@@IDENTITY'
SELECT SCOPE_IDENTITY () 'SCOPE_IDENTITY ()'
SELECT IDENT_SEED ('Test') 'IDENT_SEED ()'
SELECT IDENT_INCR ('Test') 'IDENT_INCR ()'
SELECT IDENT_CURRENT ('Test') 'IDENT_CURRENT ()'
SELECT * FROM Test
--Results:

@@IDENTITY
---------------------------------------
8

SCOPE_IDENTITY ()
---------------------------------------
8

IDENT_SEED ()
---------------------------------------
2

IDENT_INCR ()
---------------------------------------
3

IDENT_CURRENT ()
---------------------------------------
8

ID    NAME
----------- -----
2       A
5       B
8       C


Similar Articles