Use guid.comb in your database if you need guid keys but don’t want to take a big performance hit

COMB Guids have been around since 2002. In short, WinNT used to use the MAC address of a network card to help generate GUIDs, and they were pseudo-sorted. Since then, Guid have become more random and not dependent on anything. Because of this, SQL Server 2000+ incurs a significant performance degradation when you use Guids as primary keys. The degradation can be up to a factor of 10. The reason is the random nature of the Guid and the clustered index on the primary key (which seeks to keep them in sequence).

We must remember that a Guid is not a string even thought that’s how we see them. A Guid is a 16-byte data structure. An int is a 4-byte data structure, so we can simply think of it as 4 times as large. The kicker is that integer keys are sorted and incremental. Guids are random. A clustered index can keep them in order for you, but every insert incurs the job of finding where in the list to insert the new value. Integer keys just go at the end of the list.

If you are using Guids (and NHibernate), check out the guid.comb key generator. Read Jimmy Nilsson’s article on COMB Guids to understand just why they perform better than normal Guids, and then go through the NHibernate documentation to see how you can use them in your mappings. Ok, I’ll just give you a sample: