Identity Column as Primary Key

Question

Could you please opine if having identity column as primary key is a good practise? For ORM tools, having identity column on tables helps. But there are other side effects such as accidental duplicate insertion.

Thanks Nayn

Answer 1

Yes, using a INT (or BIGINT) IDENTITY is very good practice for SQL Server.

SQL Server uses the primary key as its default clustering key, and the clustering key should always have these properties:

• narrow
• static
• unique
• ever-increasing

INT IDENTITY fits the bill perfectly!

For more background info, and especially some info why a GUID as your primary (and thus clustering key) is a bad idea, see Kimberly Tripp's excellent posts:

• GUIDs as PRIMARY KEYs and/or the clustering key
• The Clustered Index Debate Continues...
• Ever-increasing clustering key - the Clustered Index Debate..........again!

If you have reasons to use a GUID as primary key (e.g. replication), then by all means make sure to have a INT IDENTITY as your clustering key on those tables!

Marc