CODEWITHSUNDEEP
c#sql-serverormormlite-servicestack
easuter
easuter

Reputation: 1197

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.

Upvotes: 1

Views: 412

Answers (2)

rsafier
rsafier

Reputation: 11

This might alleviate your need for a non-clustered index.

I used a Converter to get sequential Guid's SQL Server likes in its clustered indexes for new insert. 

public class SequentialSqlServerGuidConverter : SqlServerGuidConverter 
{
    public override object ToDbValue(Type fieldType, object value)
    {  
        if (value is Guid && value.Equals(Guid.Empty))
        {
            var newGuid = SequentialGuidGenerator.NewSequentialGuid(SequentialGuidType.SequentialAtEnd);
            return newGuid;
        }
        return base.ToDbValue(fieldType, value);
    }
}

The SequentialGuidGenerator code can be found here: http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

It obviously has the side effect of changing all values of Guid.Empty to a new SequentialGuid. In practice, we are not trying to find rows which equal Guid.Empty so it isn't an issue.

Upvotes: 1

mythz
mythz

Reputation: 143349

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.

Upvotes: 1

Related Questions