How to create clustered indexes using Fluent NHibernate?

Question

I use Fluent-NHibernate (with automation) to create my tables, but would like to select a different clustered index than the ID field, which is used by default. How to create clustered indexes with Fluent NHibernate in a field other than the default field of the main field?

The main reason for this is simple. I use Guides for my primary key fields. By default, NHibernate creates clustered indexes in primary key fields. Because guides are usually not sequential, clustering in the primary key field causes performance problems.

As we all know, adding records at the end of a table is much cheaper than inserting records into a table. In addition, the records in the table are physically stored in the order of the elements in the cluster index. Since the guides are somewhat “random” and not sequential, new Guides can be generated that are less than the value of other identifiers already in the table, which leads to the insertion of tables, and not to the addition.

To minimize this, I have a column called CreatedOn that is of type DateTime. I need the table to be clustered in this CreatedOn column so that all new records are added, not inserted.

Any ideas on how to do this are welcome !!!

Note. I understand that I can use Sequential Guids, but I prefer not to go this route for security reasons.

---

Note. I still don't have an answer to this post, but I have a few ideas that I'm thinking about right now.

• Using NHibernate without Fluent, I think it's possible to create clustered indexes directly in NHibernate. I still don't know enough about NHibernate to know how to do this. I'm just pretty (almost almost absolutely) sure that this can be done.

• Fluent-NHibernate is used to enable a method of setting attributes (such as a clustered index) on an SQL object until recently rewritten. Now this option has disappeared. I would probably ask if this option is available. If so, I could probably use this to set up a clustered index.

• Fluent-NHibernate , . , , , .

• SQL script . . . , NHibernate "" ? 2. NHibernate, , ? , . . SQLServer2008 MySQL. NHibernate , . , .

• , , IPropertyInstance, , Index, . , , . , . , Fluent-NHibernate.

Answer 1

, , - . MS SQL Server. , , .

NHibernate CLUSTERED . SQL Server. CLUSTERED, CLUSTERED .

, , - , PrimaryKeyString. NHibernate - Dialect.cs:

    public virtual string PrimaryKeyString
    {
        get { return "primary key"; }
    }

SQL Server

    public override string PrimaryKeyString
    {
        get { return "primary key nonclustered"; }
    }

SQL Server NONCLUSTERED. CLUSTERED XML.

<database-object>
  <create>
    create clustered index IX_CustomClusteredIndexName on TableName (ColumnName ASC)
  </create>
  <drop>
    drop index IX_CustomClusteredIndexName ON TableName
  </drop>
</database-object>

Answer 2

, , .

NHibernate, . , , .

Answer 3

, , guid.comb, PK uids , Guid , , .

Jeffrey Palermo .

, - ?

Answer 4

@abx78, , knowledgde . 3 "Fluent NHibernate ":

After the configuration has been built (thus the mappings are analyzed), Fluent NHibernate gives us the opportunity to view the actual mappings with configuration.ClassMappingsand configuration.CollectionMappings. The latter is used in the example below to set the composite primary key, resulting in a clustered index in Sql Server (as @ abx78 points out):

foreach (var collectionMapping in configuration.CollectionMappings) {
    // Fetch the columns (in this example: build the columns in a hacky way)
    const string columnFormat = "{0}_id";
    var leftColumn = new Column(string.Format(
        columnFormat,
        collectionMapping.Owner.MappedClass.Name));
    var rightColumn = new Column(string.Format(
        columnFormat,
        collectionMapping.GenericArguments[0].Name));
    // Fetch the actual table of the many-to-many collection
    var manyToManyTable = collectionMapping.CollectionTable;
    // Shorten the name just like NHibernate does
    var shortTableName = (manyToManyTable.Name.Length <= 8)
                                ? manyToManyTable.Name
                                : manyToManyTable.Name.Substring(0, 8);
    // Create the primary key and add the columns
    // >> This part could be changed to a UniqueKey or to an Index
    var primaryKey = new PrimaryKey {
        Name = string.Format("PK_{0}", shortTableName),
    };
    primaryKey.AddColumn(leftColumn);
    primaryKey.AddColumn(rightColumn);
    // Set the primary key to the junction table
    manyToManyTable.PrimaryKey = primaryKey;
    // <<
}

Source: Fluent NHibernate: How to create a clustered index on a table with many of many?