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

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.


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

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
    public int Id { get; set; }

    public int? CountryId { get; set; }

    public Country Country { get; set; }

    public string Name { get; set; }

    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)]
    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
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.


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.

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