EntityFramework - create clustered index on non-primary key

The primary key will be created as a clustered index, and only one clustered index per table is permitted.  Lots of legerdemain will be required, if it is even possible at all.  You may succeed if you remove the indexes from both fields ans remove the primary key.  Index the identity field first with a clustered index.  Index the GUID field Next.  Then make it the PK.  Only one clustered index is permitted.  If the PK is created and a clustered index does not already exist, the PK index will be created as a clustered index by default.  Careful attention to the order in which things get done may allow you to create the odd configuration you desire.

This is not easily possible, cause the Code-First approach means, that you don't bother the database implementation details. Here's the connect item to vote for.

Another option would be a custom database initializer, but this is imho pretty overkill. I would either go for an database-first approach here, cause you care for implementation or go the easy way like Nick said. Create a T-SQL script to be run after database initialization, which recreates the primary and clustered keys as you want.

Like it said above there can be only clustered index by table, since the clustered index is the way the records will be physically ordered.
I'm just wondering why do you need a GUID and an Identity ID? Why can't you work with an ID column only?

Hi:

Thanks for your comments.  I understand that only one clustered index is permitted per table.  It is my understanding that  that indexes based on integers are more efficient than on Guids and that clustered indexes do not have to be on the primary key and I need the Guid as my primary key.  I would be fine just removing the index from the Guid.

Where I am stuck right now is using CodeFirst to remove the clustered index from the primary key.  Any help in doing that would be greatly appreciated.

RBS

Still can't understand why you just don't drop the GUID column.
About the CodeFirst, I'm sorry but I can't help. Never worked with that. I'm just a SQL Server guy.

I have a specification that the primary key is a Guid.

Ok but don't forget that the foreign keys should also be GUIDs.
So, when are you expecting to use the identity column ID?

Ok,
Looking around regarding CodeFirst, I've read

We can create the database one of two ways:
1.Manually create and define the schema ourselves using a database tool (e.g. SQL Management Studio or Visual Studio)
2.Automatically create and generate the schema directly from our model classes using the EF Code-First library

http://weblogs.asp.net/scottgu/code-first-development-with-entity-framework-4

You are doing #2, but not necessarily getting the design you want.

But it looks very much like a C# environment.
So, I don't see any technical reason that you couldn't create a C# class to manipulate this table after CodeFirst creates it.
Or, for that matter, to use c# to create it the way you want it in the first place.
Is there some administrative or technical hurdle that prevents you from doing so?

No - just that I don't know how I to use C# to drop the index after codefirst creates it.

I've requested that this question be closed as follows:

Accepted answer: 0 points for RBS's comment #a40640045

for the following reason:

my original question was never answered

The second entry in the google search
'c# drop index from table'
has a nice example of using C# to drop and recreate a primary key.

As we have said, you are only permitted one clustered index per table, so your requirement is going to mean either working with SQL Server Management Studio or C# to manipulate the table after CodeFirst creates it.
Or to create it yourself first, since CodeFirst isn't going to meet your requirements.
There's a nice example here
http://www.homeandlearn.co.uk/csharp/csharp_s12p3.html

So
I'm trying to create a table using EF in which the primary key is a GUID and the clustered index is an auto-incrementing int column.  
Your answer is, you cannot get CodeFirst to do that.
Depending upon what your environment and requirements are you next question will be
I have operational requirements for a table created by EF to have its indexes altered as follows: The primary key must be a GUID, but the clustered index must be an int identity column.  How can I use C# to accomplish this?
or
I have operational requirements for a table created by EF to have its indexes altered as follows: The primary key must be a GUID, but the clustered index must be an int identity column.  How can I use SSMS to accomplish this?
Or
I have operational requirements for a table as follows: The primary key must be a GUID, but the clustered index must be an int identity column.  How can I use C# to create this table and then have CodeFirst use it afterward?

You have been presented with ways forward.  You just have to choose what is right for you.

See my comment:

Thanks for your comments.  I understand that only one clustered index is permitted per table.  It is my understanding that  that indexes based on integers are more efficient than on Guids and that clustered indexes do not have to be on the primary key and I need the Guid as my primary key.  I would be fine just removing the index from the Guid.

Where I am stuck right now is using CodeFirst to remove the clustered index from the primary key.  Any help in doing that would be greatly appreciated.

to which vitor replied

Still can't understand why you just don't drop the GUID column.
About the CodeFirst, I'm sorry but I can't help. Never worked with that. I'm just a SQL Server guy.

I'm happy to give/share  Vitor credit - if that's what he want.  However, he did misunderstand my question - which was about CodeFirst to which he replied:  "About the CodeFirst, I'm sorry but I can't help. Never worked with that. I'm just a SQL Server guy."

I'm happy to give/share  Vitor credit - if that's what he want

No. I just want that you close this question properly. Please check others Experts comments because I think they pointed you correctly.

OK,

If you alter the order in your class, and indicate the key, does this get it done?
http://www.codeproject.com/Articles/813912/Create-Primary-Key-using-Entity-Framework-Code-Fir

public class LearningObject
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Index("ClusteredId", IsClustered = true)]
        public int ClusteredId { get; set; }

       
        [Key]
        public Guid Id { get; set; }

        [MaxLength(50)]
        public string Name { get; set; }

        public int ScoId { get; set; }
    }

Select allOpen in new window

My apologies to those who responded - Looking at my original question - it wasn't clear - I was trying to take an existing table that had a Guid as a clustered index and substitute an integer as the clustered index/unique key and I was trying to do it this with code first.  Every way that I tried doing it I would get an error.

The solution from Nick67 gave me the same error that I kept getting with different but similar versions:

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

Actually, I don't have an existing table - I got the error when I tried to create a new table with the code you sent me.  That being said, I would like to thank you for your help - I moved on to use another approach.