Clustered Indexes and SQL Azure - SQL Azure Team Blog - Site Home - MSDN Blogs

Clustered Indexes and SQL Azure

Clustered Indexes and SQL Azure

Rate This
  • Comments 14

We are going to start a new series of posts focusing on the basics of SQL Azure and build on top of these to give you more detailed information about building and migrating applications to SQL Azure.

Unlike SQL Server, every table in SQL Azure needs to have a clustered index. A clustered index is usually created on the primary key column of the table. Clustered indexes sort and store the data rows in the table based on their key values (columns in the index). There can only be one clustered index per table, because the data rows themselves can only be sorted in one order.

A simple table with a clustered index can be created like this:

CREATE TABLE Source (Id int NOT NULL IDENTITY, [Name] nvarchar(max), 
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
))

SQL Azure allows you to create tables without a clustered index; however, when you try to add rows to that table it throws this error:

Msg 40054, Level 16, State 1, Line 2

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

SQL Azure does not allow heap tables – heap tables, by definition, is a table that doesn't have any clustered indexes. More about SQL Server indexes in this article on MSDN.

Temporary Tables

That is the rule for all permanent tables in your database; however, this is not the case for temporary tables.

You can create a temporary table in SQL Azure just as you do in SQL Server. Here is an example:

CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max))
-- Do Something
DROP TABLE #Destination

Do you have questions, concerns, comments? Post them below and we will try to address them.

  • Good idea for a post series, but a little bit of technical info as to the "why" of these things would be nice too.

    >> Unlike SQL Server, every table in SQL Azure needs to have a clustered index.

    What about SQL Azure drives the need for this requirement?

  • Is this still a requirement? Doesn't seem like it.

  • One question... EF uses Non-Clustered Indexes automatically for Associations. Is there a way to have EF generate Clustered so it is compatible with Azure?

  • Generally clustered indexes are not recommended on PK column in DB design (see sqlserverpedia.com/.../Indexing_Strategies for example). So why does the post say:

    "A clustered index is usually created on the primary key column of the table. "

  • Clustered indexes are very much recommended on primary key columns (even according to the article you posted). That article just does not recommend them on identity columns. In fact I would go further and not recommend that identity columns are primary keys at all (assuming there is another natural primary key available).

  • Can I just say...this is a huge pain.  The fact that Microsoft decided to have inconsistencies between Azure and MS SQL makes no sense.  I've spent 4 hours so far trying to figure out how to migrate my stupid databases.  I think I'm going to give up until Microsoft decides to make the products actually talk to each other.  I'm scared to death to think of what might pop up if I wanted to move to production....

  • I am trying to export my database from my SQL Server to azure. Table is having a primary key defined, So Clustered index is there, yet azure is not accepting the data and throwing this stupid error:

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

    Can someone from blog team comment about this ?

  • I am trying to  removing primary key on my table in sql azure database, but  the error is tables without create clustered index are not supported in this version of sql server. please create clusterd index and try again . can any one me about this error?

  • I get the 'table does not have a clustered index' error on tables that do indeed have a clustered... go figure.

  • Does no one at Microsoft respond to the questions, comments, concerns, that they solicited in their own blog post?

  • How can I pull in a temp table using a file that is currently on my desktop?

  • The Destination and Source both Tables Should have clustered index, Check your Target

    Table , Does it contains the Clustered index ?

  • is this still an issue? so I could not deploy data mining cases and table structures in an SQL database which has no indexes at all! it was a total surprise for me!

    /luchof

  • Any comments from MS when we can expect to get rid of this constraint at least in blob files. Microsoft? Thanks.

Page 1 of 1 (14 items)
Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post