My previous article emphasized that you shouldn’t mix clustered indexes and identity fields. However, if you’re using NHibernate you already probably know you shouldn’t be using identity fields. On the other hand, the points still generalize to some of the NHibernate generators:
- Increment: just as bad as identity
- HiLo: better than identity, but not by much. Don’t mix with a clustered index.
- GUID: Extremely random. In fact, probably too random. The inserts get plastered everywhere and can hurt performance.
- GUID Comb: Better than GUID,
In short: still don’t put a clustered index on an identity field. Modifying GUID comb to include the thread ID might actually make it viable.
Again, we’re talking OLTP here. If you read the original article, Jimmy Nilsson measures batch insert performance, and the concurrency implications never come up.
Technorati Tags: NHibernate,SQL Server,Indexes,Performance