SQL Azure: Tables without a clustered index are not supported in this version of SQL Server

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


The MSDN explains that "a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table."


We have been encountering this error after we've migrated to SQL Azure. Sure enough, there is a tool to migrate from SQL Server to SQL Azure, but we have a feature that dynamically creates a table in the database. The task is written in a stored procedure and the script is for SQL Server, so it is not covered by the migration tool. Once the stored procedure is executed (creates a table), it does not automatically add a clustered index, producing the error above when we insert a record in the newly created table.


How do we solve the issue?

First, get all user tables without clustered index

USE ASampleDatabase

SELECT name

FROM sys.objects

WHERE type = 'U'

      AND object_id

            NOT IN (SELECT object_id FROM sys.indexes WHERE index_id = 1)

Example Results:

Table1
Table2

Then, add a clustered index by performing "alter" statement to the tables. Here's the example for Table1:

ALTER TABLE[dbo].Table1

      ADD CONSTRAINT [PK_Table1]

      PRIMARY KEY CLUSTERED

(

    [PrimKey] ASC

)

Of course, we can create a more elegant script to automatically add clustered indexes to those affected tables, but the example above is already enough to get us started.


Stwaf! ^_^