SQL Azure not recognizing my clustered Index

Question

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

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

My problem is I do have a clustered index on that table. I used SQL Azure MW to generate the Azure SQL Script.

Here's what I'm using:

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 doesn't SQL Azure recognize my clustered Key? Is my script wrong?

score 4 · Accepted Answer · edited Jul 07 '15 at 18:58

4

Your script only creates the table if it did not exist yet. Perhaps there still is an old version of the table without a clustered index? You can check with:

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

If the table exists without the clustered index, you can add one like:

alter table tblPasswordReset add constraint
    PK_tblPasswordReset primary key clustered

As far as I can see, your statement does conform to the Azure create table spec.

edited Jul 07 '15 at 18:58

Dene

578
5
9

answered Aug 28 '11 at 21:32

Andomar

232,371
49
380
404

When I run that select script I do get a result for PK_tblPasswordReset type_desc = CLUSTERED. This is weird... – Nate Aug 28 '11 at 21:57
Is there a trigger on the table? `select * from sys.triggers where object_id = object_id('tblPasswordReset')` The trigger might do an insert on an unclustered table. – Andomar Aug 28 '11 at 22:00
I've just run your exact script on a SQL Azure DB and it ran fine. I suspect that the issue lies with something else in your DB conflicting with this script. Maybe a previously created version of the table? – knightpfhor Aug 28 '11 at 22:58
@Andomar This table doesn't have any triggers – Nate Aug 29 '11 at 02:30
@knightpfhor I dont have any problems running the script and creating the table. It's when I try to insert a row in the DB through .NET that I get the error. I'm thinking that I have some other version of the table, but I don't know how to check for that. – Nate Aug 29 '11 at 02:31
It's a long shot, but maybe your code is not pointing at the DB you think it is. Just to experiment, try dropping the table and running the code, you should get a completely different error. – knightpfhor Aug 29 '11 at 21:18

score 2 · Answer 2 · answered Dec 15 '12 at 04:51

Be careful if you're using SSIS. I ran into this same problem, myself, but was using SSIS instead of manually inserting the data. By default SSIS will drop and recreate the table, so even though I had it properly defined with a clustered index, my SSIS script failed. On the "Edit Mappings" step in the SSIS wizard you can manually define the table creation script. I just deleted the table gen script there and my import worked.

(I'd leave this as a comment but my post count is too anemic)

SQL Azure not recognizing my clustered Index

2 Answers2

Linked