How can you create Clustered Indexes with Fluent NHibernate?

Question

I am using Fluent-NHibernate (with automapping) to generate my tables but would like to choose a different clustered index than the ID field which is used by default. How can you create clustered indexes with Fluent NHibernate on a field other than the default Primary Key field?

The primary reasoning behind this is simple. I am using Guids for my primary key fields. By default, NHibernate creates clustered indexes on the primary key fields. Since Guids are usually not sequential, clustering on the primary key field causes a performance issue.

As we all know, appending records at the end of a table is a much cheaper operation than inserting records within the table. Also, the records in the table are physically stored in the order of the items in the clustered index. Since Guids are somewhat "random" and are not sequential, new Guids may be generated that are less than the value of other Id Guids already in the table--resulting in table inserts rather than appends.

To minimize this, I have a column called CreatedOn which is of type DateTime. I need for the table to be clustered on this CreatedOn column so that all new records are appended rather than inserted.

Any ideas for how to accomplish this are welcome!!!

Note: I realize that I could use Sequential Guids but prefer not to go down that path for security reasons.

---

Note: I still do not have an answer for this post but I have a few ideas I am pondering at the moment.

1. Using NHibernate without Fluent, I think it may be possible to created clustered indexes directly in NHibernate. I don't yet know enough about NHibernate to know how to do this. I am just pretty (as in almost absolutely) sure it can be done.

2. Fluent-NHibernate used to include a way to set attributes (e.g. like a clustered index) on a SQL object before the recent rewrite. Now that option appears to have gone away. I will probably post a question somewhere to see if that option is still available. If so, I could probably use that to set the clustered index.

3. Fluent-NHibernate provides the ability to expose a configuration for manual editing once it has been fluently built. I have not tried this functionality but expect it may offer the level of granularity that is needed to set clustered indexes.

4. Worst case scenario, I can write a SQL script to change the clustered indexes on all my tables once they are generated. However, I have a couple of questions regarding this approach. A. Since I am using automatic schema generation, will NHibernate "undo" my clustered index changes the next time it evaluates the configuration? 2. Will NHibernate error if it detects the clustered index has been changed? I need to test this but have not done so just yet. I really hate this solution though. I am testing my DB against SQLServer2008 and MySQL. Part of the beauty of NHibernate is that it is database agnostic. Once we introduce scripts, all bets are off.

5. There is an interface that is used in fluent conventions called IPropertyInstance Classes which inherit from this interface have an Index property which allows an Index to be created on the field. The problem is that there is no flag or other option to allow the index to be created as clustered. The simplest solution would be to add a property to this method to allow for clustered indexes to be created. I think I may suggest this to the Fluent-NHibernate developers.

Answer 1

I can't answer that specifically, but I'll give you some database info since I'm here.

You'll need to tell NHibernate to create the primary key at a non-clustered index. There can only be only clustered index per table, so you need to create the table as a heap, and then put a clustered index on it.

Answer 2

As you said yourself, another option is to switch to the guid.comb ID generation strategy where PK uids are based on a part which is a Guid and a part which ensures that the generated IDs are sequential.

Check out more info in a post by Jeffrey Palermo here.

But you mention that do not want to do that for security reasons - why is that?