SQL Azure does not recognize my clustered index

Question

I get the following error when I try to insert a row into an Azure SQL table.

Tables without a clustered index are not supported in this version of SQL Server. Create a clustered index and try again.

My problem: I have a clustered index in this table. I used Azure MW SQL to create Azure SQL Script.

Here is what I use:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblPasswordReset]') AND type in (N'U')) DROP TABLE [dbo].[tblPasswordReset] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblPasswordReset]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblPasswordReset]( [PasswordResetID] [int] IDENTITY(1,1) NOT NULL, [PasswordResetGUID] [uniqueidentifier] NULL, [MemberID] [int] NULL, [RequestDate] [datetime] NULL, CONSTRAINT [PK_tblPasswordReset] PRIMARY KEY CLUSTERED ( [PasswordResetID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ) END GO 

Why does SQL Azure not recognize the cluster key? Is my script wrong?

Answer 1

Your script only creates a table if it does not already exist. Maybe there is an old version of the table without a clustered index? You can check:

 select * from sys.indexes where object_id = object_id('tblPasswordReset') 

If the table exists without a clustered index, you can add it like this:

 alter table tblPasswordReset add constraint PK_tblPasswordReset primary key clustered 

As far as I understand, your statement conforms to the Azure create table specification.

Answer 2

Be careful if you use SSIS. I myself ran into this problem, but used SSIS instead of manually inserting data. By default, SSIS discards and recreates the table, so although I correctly defined it using the clustered index, my SSIS script failed. In the "Edit Mappings" step in the SSIS wizard, you can manually define the creation of the script table. I just deleted the gen script table there, and my import worked.

(I would leave this as a comment, but my zip code is too anemic)