3

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

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228

2 Answers2

6

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
  • 9,563
  • 6
  • 45
  • 58
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

Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • 13
    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 Dec 20 '09 at 13:39
  • 3
    +1 @DoronYaacoby . i was calling for a session.save in a for loop for 100.000 times for test. and with identity -> 77000 miliseconds. with hilo -> 1900 miliseconds. – kommradHomer Mar 08 '12 at 13:38
  • HiLo or Guid.Comb is faster than identity because you don't need to make a roundtrip to and from the database after every insertion. – hyde Jan 16 '16 at 19:16