6

Using the Entity Framework 6.1 code first model, what is the best way to go about changing the clustered index on a table from the default ID to another set of columns. Azure doesn't allow a table without a clustered index.

  public partial class UserProfile 
  {
    public override Guid ID { get; set; }

    [Index( "CI_UserProfiles_UserID", IsClustered = true)]
    public Guid UserID { get; set; }

    [Required]
    public Guid FieldID { get; set; }

    [Required]
    [StringLength(400)]
    public string Value { get; set; }
 }

On the table UserProfiles, ID is already the primary key and clustered index. Adding 

[Index( "CI_UserProfiles_UserID", IsClustered = true)]

to UserID creates this migration:

CreateIndex("dbo.UserProfiles", "UserID", clustered: true, name: "IX_UserProfiles_UserID");

Executing the migration generates the following error:

Cannot create more than one clustered index on table 'dbo.UserProfiles'. Drop the existing clustered index 'PK_dbo.UserProfiles' before creating another.

Improve this question
4
  • Wouldn't it be better to add an non clustered index on UserID? Usually having the pk as the clustered index is best. (since it is usually used in joins etc.) Commented Nov 17, 2015 at 20:05
  • @Magnus I don't think so. With the GUID ID as the clustered index, a users profile information will be scattered across the disk requiring disk access for each piece of the user's profile information. A clustered index on the UserID will yield retrieval of the user profile information in potentially a single disk access. Commented Nov 17, 2015 at 20:12
  • Are you saying you are only using this table for UserId lookup and never with join's with other tables on the ID column? Commented Nov 17, 2015 at 20:24
  • Pretty much, @Magnus, except for maintenance operations and possibly deleting a single row. Commented Nov 17, 2015 at 20:26

4 Answers 4

Reset to default
7
+50

To solve your problem, after you generate your migration file, you must modify the generated code by disabling clustered index for your primary key by assigning false as a value of clustered parameter of PrimaryKey.

After your modifications you must have something like this into your migration file:

CreateTable(
    "dbo.UserProfiles",
    c => new
        {
            Id = c.Guid(nullable: false),
            UserID = c.Guid(nullable: false),
            FieldID = c.Guid(nullable: false),
            Value = c.String(nullable: false, maxLength: 400),
        })
    .PrimaryKey(t => t.Id, clustered: false)
    .Index(t => t.UserID, clustered: true, name: "CI_UserProfiles_UserID");

This is not done in OnModelCreating method by using Fluent API like Manish Kumar said, but in migration file. The file that is created when you use Add-Migration command.

Existing Database

As you say in comments, your database already exist. After executing Add-Migration command, you will have this line on your DbMigration file in your Up() method:

public override void Up()
{
    CreateIndex("dbo.UserProfiles", "UserID", clustered: true, name: "CI_UserProfiles_UserID");
}

You must modify the Up() method to have this code:

public override void Up()
{
    this.Sql("ALTER TABLE dbo.UserProfiles DROP CONSTRAINT \"PK_dbo.UserProfiles\"");
    this.Sql("ALTER TABLE dbo.UserProfiles ADD CONSTRAINT \"PK_dbo.UserProfiles\" PRIMARY KEY NONCLUSTERED (Id);");
    this.CreateIndex("dbo.UserProfiles", "UserID", clustered: true, name: "CI_UserProfiles_UserID");
}

In the code above I assumed that the created clustered index is named PK_dbo.UserProfiles in your database. If not then put at this place the correct name.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is a great solution if the table has not yet been deployed to a production environment, but of course since the table already exists I get the error 'There is already an object named 'UserProfiles' in the database.'
I tried to edit your post with the full solution but the reviewers rejected the edit. If you want to copy the missing parts into your answer, I'll accept your answer. @CodeNotFound
Yep I didn't know it is an existing database. I edited my code to follow your constraint with a best and simple solution. Hope it will help you.
I don't think this will work on Azure, I believe the DROP CONSTRAINT will fail because Azure requires all tables to have a clustered index.
Hum.. You are right :) Anyway I think your answer is the best then. I think i will leave my answer for those who will have the same problem as you but not in azure ;-)
2

This is truly an area where EntityFramwork (Core) had to advance and it still is hard.

So, I could not use IsClustered(false) for my GUID / string Primary keys, for the simple reason, the project having DbContexts was DB - agnostic. So you needed to Add EntityFrameworkCore.SqlServer and IsClustered is available then, and only.

So, my solution was simple. Add no nuget package but this attribute. This ONLY works on EF Core.

I have tested this on SQL. Though, not sure if the other providers would allow this string not having any meaning. (e.g. SQLite does not know clustered indexes)

 p.HasKey(k => k.Id).HasAnnotation("SqlServer:Clustered", false);
Improve this answer

Comments

1

You need to remove the existing clustered index from your current PK 'ID' which is created by default for any "KEY" property in code first. It can be done using fluent API:

.Primarykey(x=>x.ID,clustered:false)

Once existing clustered index is removed from ID, your migration to add the clustered index on UserID should run smoothly.

Improve this answer

3 Comments

Am I missing something here? modelBuilder.Entity<UserProfile>().PrimayKey(x => x.ID, clustered: false); 'System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<MyProj.DBv2.UserProfile>' does not contain a definition for 'PrimayKey'
Is it a typo? I dont see 'r' in PrimaryKey in your comment.
It doesn't contain a definition for either PrimayKey or PrimaryKey. I can't find the fluent PrimaryKey method documented anywhere.
1

After the migration file is created, modify the generated code, disabling the clustered index for the primary key by setting the clustered property to false.

Being that Azure does not allow a table without a clustered index, and there is no utility in SQL Server to 'change' a clustered index on a table, it is necessary create a new table with the clustered index and migrate the existing data to it. The code below renames the original table, migrates the data to the new table that was created with the new clustered index and drops the original table.

        RenameTable("dbo.UserProfiles", "UserProfiles_PreMigrate");

        CreateTable(
            "dbo.UserProfiles",
            c => new
            {
                Id = c.Guid(nullable: false),
                UserID = c.Guid(nullable: false),
                FieldID = c.Guid(nullable: false),
                Value = c.String(nullable: false, maxLength: 400),
            })
            .PrimaryKey(t => t.Id, clustered: false)
            .Index(t => t.UserID, clustered: true, name: "CI_UserProfiles_UserID");

        Sql(@"
            INSERT [dbo].[UserProfiles]
            (ID,
             UserID,
             FieldID,
             Value)
            SELECT
             ID,
             UserID,
             FieldID,
             Value
            FROM dbo.UserProfiles_PreMigrate

        ");

        DropTable("UserProfiles_PreMigrate");

Any existing table constraints will be lost in this operation, so it will be necessary to recreate and indexes,foreign keys, etc on the table.

Improve this answer

1 Comment

I edited my answer with a simple solution without migrating data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.