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