asp.net Membership tables in sql azure. PK by default nonclustered

Question

SQL Azure only supports clustered Primary keys. The SQL membership provider for asp.net creates its tables with nonclustered primary keys, because they are GUIDs (the reason for that is beyond the scope of this question).

What is the quickest and easiest way to make those standard Membership tables compatible with SQL Azure? Or does SQL Azure not support GUID PK's?

This is for an already created MVC3 project that we would like to migrate to Azure.

UPDATE

SQL Azure does actually support nonclustered PK's, but it requires at least one Clustered Index. If you are using SSMS locally to manage your azure DB, be sure to update to the latest version or you'll get some confusing errors.

danludwig danludwig · Accepted Answer · 2012-01-06T14:17:12

We do this. The easiest way is to use aspnet_regsqlazure. Here are some links:

http://tofutim.wordpress.com/ http://archive.msdn.microsoft.com/KB2006191/Release/ProjectReleases.aspx?ReleaseId=3539

Update

A quick glance at our sql azure membership db shows that the uniqueidentifier pk's are indeed nonclustered:

CREATE TABLE [dbo].[aspnet_Applications](
    [ApplicationName] [nvarchar](256) NOT NULL,
    [LoweredApplicationName] [nvarchar](256) NOT NULL,
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [Description] [nvarchar](256) NULL,
    PRIMARY KEY NONCLUSTERED 
    (
        [ApplicationId] ASC
    ),
    UNIQUE NONCLUSTERED 
    (
        [LoweredApplicationName] ASC
    ),
    UNIQUE NONCLUSTERED 
    (
        [ApplicationName] ASC
    )
)
GO

ALTER TABLE [dbo].[aspnet_Applications] ADD  DEFAULT (newid()) FOR [ApplicationId]
GO

asp.net Membership tables in sql azure. PK by default nonclustered

1 Answers