Sql Server : Common Misconceptions Regarding IDENTITY Columns

• Small group of developers think, that IDENTITY is a data type.

IDENTITY isn’t a data type; it’s a column property that you can declare on a whole number data type such as tinyint, smallint, int, bigint, or numeric/decimal

• IDENTITY property can be assigned to as many columns in a table, as you want.

Only ONE column in a table can be assigned IDENTITY property. That’s why following query is also valid, to get identity column (even without providing name of column in select statement).

USE AdventureWorks

SELECT IDENTITYCOL FROM HumanResources.Department

•  IDENTITY property ensures uniqueness among column values

IDENTITY column never guarantees unique value in a column. Though values generated by IDENTITY are always unique.

• One cannot insert explicit value for identity column in table

Explicit value for identity column is possible. How? Find it here in early post.

• @@IDENTITY, contains the last identity value used by that table/column

It’s not true. @@IDENTITY actually contains the last identity value used by that CONNECTION. If you have multiple tables with IDENTITY COLUMNS and if multiple INSERT statements are carried out in a batch on the same or different tables, the @@IDENTITY has the value for the last statement only. 

IDENT_CURRENT(tablename) is the best alternative. It will return accurte value used by IDENTITY column of your desired table. 

SELECT IDENT_CURRENT('HumanResources.Department')