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’)
Advertisements

Posted on December 15, 2010, in Sql Server Internals, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: