Clustered Indexes and SQL Azure

[This article was contributed by the SQL Azure team.]

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.