HiLo or identity?

Question

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

Answer 1

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.

Answer 2

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