2

Closed

UniqueIdentifier primary keys (GUIDs) should not be created as Clustered Indexes

description

Hey guys,

In my original request on Connect ( https://connect.microsoft.com/VisualStudio/feedback/details/587040/create-guid-ids-as-non-clustered-primary-keys-in-entity-framework ) I made the point that GUIDs should not be created on the db side as a Clustered Index, possibly very poorly at the time. Basically, GUIDs when inserted in as a clustered index primary key means that the table gets a perf hit as it needs to re-sort and insert the new row where appropriate.

The work item you redirected me to shows your plan is to use sequential uniqueidentifiers to "fix" this: http://entityframework.codeplex.com/workitem/71

Instead I'd like you to discuss just creating it as a Non-clustered Index Unique Constraint on the database side - you still get the primary key, you still get it indexed but you don't suffer the performance hit on the database - if this isn't the case as I believe and have read and been told by SQL MVP's then I'd be happy if you loop someone in on the discussion to correct me.

That way we can still create random GUIDs on the client side, we can also insert new records in multiple instance of the database and not have replication concerns, and we don't need to resort to using sequential uniqueidentifiers on the database.
Closed Mar 25, 2013 at 6:16 PM by ajcvickers
See comments section.

comments

ajcvickers wrote Oct 30, 2012 at 5:19 PM

Investigate the impact of cluster/non-clustering.

glennc wrote Dec 13, 2012 at 10:42 PM

Assigned to EF 6

ajcvickers wrote Dec 14, 2012 at 11:10 PM

This was discussed in yesterday's design meeting. The notes contain initial decisions that were made for this feature: http://entityframework.codeplex.com/wikipage?title=Design%20Meeting%20Notes%20-%20December%2013%2c%202012

Thanks,
Arthur

ajcvickers wrote Jan 18, 2013 at 12:56 AM

ajcvickers wrote Mar 25, 2013 at 6:15 PM

This was discussed again and we decided for several reasons to not make any additional changes here beyond the change already made to add clustering as part of the Migrations fluent API. The reasons for this are:
  • The SQL generated from Migrations for SQL Azure will not work for the common case of a GUID primary key. We could special case SQL generation for Azure to still generate a clustered index, but this would mean that if you want a non-clustered index on Azure you would have to override the SQL generator to do this, even though "clustered" is being passed as false in the Migration code. It is entirely reasonable to want a non-clustered index on Azure for cases where you have another column with a clustered index, so it should be easily possible to do this.
  • It is reasonable to want a clustered index in some situations when using a client-generated GUID key. For example, it is a known pattern to use a sequential GUID generator on the client. This doesn't seem common for EF currently, likely because EF doesn't have such a built-in generator, but it could get one in the future.
  • Doing this would add complexity to the mental model of when a clustered index is generated and when it isn't. It's not clear that the value added is worth this additional complexity in the mental model.
What this means is that as it stands client-generated GUID primary keys will have "clustered" set to true by default just like other keys. To get a non-clustered index, just change the PrimaryKey call in the Migration to include "clustered: false". For example:
CreateTable(
    "dbo.Posts",
    c => new
        {
            Id = c.Guid(nullable: false),
            Title = c.String(),
            BlogId = c.Guid(nullable: false),
        })
    .PrimaryKey(t => t.Id, clustered: false) // <-- Added "clustered: false" here
    .ForeignKey("dbo.Blogs", t => t.BlogId, cascadeDelete: true)
    .Index(t => t.BlogId);