Just wanted to get some opinions on primary keys - would it be better to use identity/sequence numbers or use a HiLo strategy (query for the high value and increment the low value on the app itself)?
A:
If your application is only going to use one database, I'd go with an identity/sequence.
The only really compelling reason to go with HiLo that I've seen is when you could have two disconnected instances of your application that people can work on simultaneously and you need to reconcile the differences at some point
Ex. You are working on a content management system where people could be working on two different servers at different locations but the two need to be sync'd at some point
Justin Niessner
2009-04-29 19:31:19
There are other very compelling reasons to use HiLo, performance being one of them. They are described here: http://nhforge.org/blogs/nhibernate/archive/2009/03/20/nhibernate-poid-generators-revealed.aspx
Doron Yaacoby
2009-12-20 13:39:41
+2
A:
If you're using SQL Server 2005/2008 with a large table (> 1 million rows), you may want to consider this, which links to this. There's an issue with scope_identity() and @@identity under certain circumstances.
Of course, designing around a current implementation issue isn't always the wisest decision.
Aaron Daniels
2009-04-29 19:42:17