1

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.

Gray
  • 115,027
  • 24
  • 293
  • 354
easuter
  • 1,167
  • 14
  • 20

2 Answers2

1

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.

Community
  • 1
  • 1
mythz
  • 141,670
  • 29
  • 246
  • 390
  • Thanks for that info. Since those attribute parameters do nothing/silently faile, why do they exist at all? – easuter Jan 20 '16 at 21:04
  • @easuter The `[Index]` attribute is only used for creating Indexes on other non-Primary Key properties. The `[PrimaryKey]` and `[AutoIncrement]` attributes can be used on Primary Keys. – mythz Jan 20 '16 at 21:16
  • 1
    I understand this, however I wasn't inquiring about creation of primary keys, I was asking why the `[Index]` attribute's `NonClustered` parameter simply fails silently, instead of for example raising a `NotImplementedException`. Why do the `NonClustered` and `Clustered` parameters exist if they don't have any purpose? – easuter Jan 20 '16 at 21:21
  • @easuter The `[Index]` attribute is ignored for PK and [does get applied when creating indexes](https://github.com/ServiceStack/ServiceStack.OrmLite/blob/a55376f3d64327b90121e8b57be827a9e3a03a79/src/ServiceStack.OrmLite/OrmLiteDialectProviderBase.cs#L1136) for non-Primary Key properties. – mythz Jan 20 '16 at 21:29
  • 2
    I see, it's using the `Id` field as the primary key although I didn't explicitly specify this with `[PrimaryKey]`. – easuter Jan 20 '16 at 21:37
1

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.

rsafier
  • 11
  • 3