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
2
- As opposed to a natural key or a surrogate key generated in the application (hi/lo, guid, etc.)?Michael Gattuso– Michael Gattuso2009-12-03 07:11:00 +00:00Commented Dec 3, 2009 at 7:11
- See stackoverflow.com/questions/262547/… as this may be a dupRippo– Rippo2009-12-03 07:21:02 +00:00Commented Dec 3, 2009 at 7:21
Add a comment |
3 Answers
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
Comments
IDENTITY keys are a good practice for server-side generated keys, in environments where you don't have replication or heavy data merging. The way they're implemented, they don't allow duplicates in the same table, so don't worry about that. They also have the advantage of minimizing fragmentation in tables that don't have lots of DELETEs.
GUIDs are the usual alternative. They have the advantage that you can create them at the web tier, without requiring a DB round-trip. However, they're larger than IDENTITIES, and they can cause extreme table fragmentation. Since they're (semi) random, inserts are spread through the entire table, rather than being focused in one page at the end.
2 Comments
richardtallent
To combat the issue of GUID fragmentation, use a COMB-style GUID rather than a standard fully-random one. On MSSQL, there is a semi-COMB implementation that is sometimes "good enough" but does not guarantee end-of-page inserts across server reboots. The space difference between int or bigint and GUID (128 bits) isn't worth worrying about in most cases.
RickNZ
In addition to not staying sequential across server reboots (if that long), the NEWSEQUENTIALID feature also doesn't prevent fragmentation or page splits; it just localizes it in one area until the value resets. In addition, NEWSEQUENTIALID can only be used as a column default, so it can only be generated on the server. Also, GUIDs generated that way are more guessable than "pure" GUIDs -- so three of the benefits of using GUIDs over IDENTITY don't hold with NEWSEQUENTIALID.
I use a Guid because it really helps when I am dealing with distributed applications. Especially when all the distributed instances also need to create new data.
Nevertheless, I don't see any problems with autoincrement integer primary keys in simple situations. I would prefer them actually because it is easier to work directly with SQL queries because it is easier to remember.
