By default, NHibernate creates the clustered index on the primary key column. This is not always the most efficient behavior. In some instances it is useful to create the clustered index on a different column than the primary key column. I wonder if anyone can explain how to accomplish this.
Here is my use case.
I have several tables which each have more than one million records. For a variety of reasons, these tables use Guids for primary keys rather than integers. The Guids are not sequential and can not effectively be made sequential.
Because generated guids for new records are not guaranteed to come after the columns max value, many of the records are being inserted rather than appended. This is because the clustered index determines the physical order of the records. Since these records are less than the maximum, other records must be moved so these new records can be inserted. This can be a performance issue.
I would like to use my CreatedOn column as the clustered index--though this column is not guaranteed to be unique. (It is possible two processes could insert records at precisely the same moment.)
How I can specify that my CreatedOn column be used as my clustered index rather than the default Id column?
Thank you!
|