CODEWITHSUNDEEP
sqlazureazure-sql-database
Jonas Arcangel
Jonas Arcangel

Reputation: 1925

Clustered Index Error in Azure for a Table that Has a Clustered Index

I am running this command on SQL Azure.

DELETE FROM dbo.Users

I am getting this error.

Msg 40054, Level 16, State 1, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

I then try to create a clustered index.

CREATE UNIQUE CLUSTERED INDEX Idx_Users ON dbo.Users(Id);

I get this error.

Msg 1902, Level 16, State 3, Line 4
Cannot create more than one clustered index on table 'dbo.Users'. Drop the existing clustered index 'PK_dbo.Users' before creating another.

Do I or do I NOT have a clustered index?

Upvotes: 3

Views: 876

Answers (1)

gimlichael
gimlichael

Reputation: 1394

I had a similar issue where I was unable to delete records from a table.

After reading this post, SQL Azure not recognizing my clustered Index, paired with your post I checked the dependencies through MSSMS on my Security_User table; a 0-n relationship table, Security_UserHistory, had a FK-reference to this table. This reference was not indexed and (strangely enough) triggered the error in question.

After applying a CLUSTERED INDEX to the FK-table Security_UserHistory referencing the PK-table Security_User the problem was solved.

enter image description here

I wish Microsoft would add some clarity in the error message, as it indicates the problem lies with the table you are deleting the rows from.

Upvotes: 1

Related Questions