Leaving Id as Primary Key but using alternate column as clustered index

ServiceStack.OrmLite
1

Hey ServiceStack,

I was wondering if it was possible to have a table keep its primary key as the Id, create it unclustered, and apply an additional index to an alternate column that is clustered.

I could do this with sql statements after the database is created, but I was hoping there was a way to do it via the code-first framework.

Thanks,
Ryan

2

We have the [Index] attribute for defining an extra index, you can specify Unique, Clustered or Non-Clustered.

3

Let me give you this example and you can tell me your expected results.

I have the following State table:

public class State : Entity<State, SeedData.State>, IAudit
{
    [AutoIncrement]
    public int Id { get; set; }

    [References(typeof(Country))]
    public int? CountryId { get; set; }

    [Reference]
    public Country Country { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Abbreviation { get; set; }

    public DateTime CreatedDate { get; set; }
    public DateTime ModifiedDate { get; set; }
    public string ModifiedBy { get; set; }
}

Now, I want to make the CountryId property a clustered index, but leave the Id as the primary key.

    [Index(Unique = false, Clustered = true)]
    [References(typeof(Country))]
    public int? CountryId { get; set; }

When I apply the index above, I receive the following error:

EXCEPTION: System.Data.SqlClient.SqlException (0x80131904): Cannot create more than one clustered index on table 'State'. Drop the existing clustered index 'PK__State__3214EC074E51E6CD' before creating another.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at ServiceStack.OrmLite.OrmLiteCommand.ExecuteNonQuery()
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.ExecuteSql(IDbCommand dbCmd, String sql, IEnumerable`1 sqlParams)
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.CreateTable(IDbCommand dbCmd, Boolean overwrite, Type modelType)
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.CreateTables(IDbCommand dbCmd, Boolean overwrite, Type[] tableTypes)
   at ServiceStack.OrmLite.OrmLiteSchemaApi.<>c__DisplayClass1.<CreateTables>b__0(IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec(IDbConnection dbConn, Action`1 filter)
   at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec(IDbConnection dbConn, Action`1 filter)
   at ServiceStack.OrmLite.OrmLiteSchemaApi.CreateTables(IDbConnection dbConn, Boolean overwrite, Type[] tableTypes)
   at Mobacomm.Platform.DataModel.Generation.Configuration.CreateDatabase(IDbConnection db) in s:\Work\Mobacomm Platform\Platform\Mobacomm.Platform.DataModel\Generation\Configuration.cs:line 187
ClientConnectionId:3c004b78-ef81-44a5-aa5f-e31d248052b3
Error Number:1902,State:3,Class:16

So my problem is that the Primary Key Index is still there, even though I apply a my own clustered index and do not explicitly define the primary key (it is there by convention). Do I

A) Have to alter the current PK Index before applying this
B) Expect OrmLite to pickup that I am applying my own clustered index and alter the PK clustered index to be non-clustered. Example:

[PrimaryKey(Clustered = false)]

This example would only be valid if an additional Clustered Index was supplied, per my [Index(Unique = false, Clustered = true)] example above

Patiently awaiting your answer to this dilemma.

Thanks,
Ryan

4

This is a non-standard RDBMS feature (i.e. not supported by most RDBMS’s) that you’ll have to manage outside of OrmLite, e.g: manually dropping the clustered index on the Primary Key then adding the clustered index.

You may also be able to leverage the Post Custom SQL Hooks to do this.

5

Excellent, thanks for the input. I’ll implement that.