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