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).
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.