Search code examples
c#sql-serverormormlite-servicestack

Creating non-clustered indexes with ServiceStack OrmLite on SQL Server 2012

I'm evaluating the use of ServiceStack's OrmLite in one of my current projects, and I require some control over the indexes that are created; I'd prefer to control as much of this via the data annotations where possible.

Unfortunately I'm having no luck forcing indexes over non-sequential IDs to be non-clustered. Using the following table model:

[Alias("Players")]
public class Player
{
    [Index(Unique = true, NonClustered = true)]
    public Guid Id { get; set; }

    [Required]
    [StringLength(128)]
    public string Url { get; set; }
}

the CreateTableIfNotExists<Player>() method seems to ignore the indication to create a non-clustered index, and created a clustered one instead (which will result in index fragmentation and poor performance):

enter image description here

What am I missing here?

Note: this is with OrmLite 4.0.52, using the SqlServer2012Dialect provider.

Solution

  • 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.