SQL Server creating table with clustered index without a primary key

Question

Is it possible to create a clustered index from a create table statement in SQL Server 2008 that is not a primary key?

The purpose of this is for a table in SQL Azure, so it is not an option for me to first create the table, and then create the clustered index on the table.

Edit: Apparently it was FluentMigrator that was causing my problems, it's version table does not have a clustered index so it was erroring trying to create the versioning table not my table.

Solution 1

Yes, it is possible to create a clustered index that is not the primary key. Just use a CREATE CLUSTERED INDEX statement.

CREATE TABLE dbo.myTable (
    myTableId int PRIMARY KEY NONCLUSTERED
    myColumn int NOT NULL
)

CREATE CLUSTERED INDEX myIndex ON dbo.myTable(myColumn)

Prior to version Azure SQL Database v12, you had to have a clustered index before you could insert any data to a table. As of Azure SQL Database v12, heaps (tables without a clustered index) are now supported.

If your database was created prior to June 2016, here are the instructions for upgrading to version 12.

Solution 2

CREATE TABLE dbo.Table_1
    (
    Id int NOT NULL IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,
    SomeOtherUniqueColumn int NOT NULL CONSTRAINT Item4 UNIQUE CLUSTERED
)  ON [PRIMARY]

note the specification of nonclustered on the primary key

This will still work.

CREATE TABLE dbo.Table_1
    (
    SomeOtherUniqueColumn int NOT NULL CONSTRAINT Item4 UNIQUE CLUSTERED
)  ON [PRIMARY]

Solution 3

The code below is compatible with Azure. It creates a primary key non-clustered and a clustered index in a single create table statement. This syntax also allows for specifying more than one column in your key.

CREATE TABLE MyTable (
    ID uniqueidentifier  NOT NULL,
    UserID uniqueidentifier  NOT NULL,
    EntryDate DATETIME NOT NULL,
    CONSTRAINT PK_MyPrimaryKey_Name PRIMARY KEY NONCLUSTERED (ID),
    CONSTRAINT UCI_MyClusteredIndexName UNIQUE CLUSTERED (UserID ASC,EntryDate ASC,ID ASC)
);

In order to change a tables clustered index, the clusteredd index must be dropped, which converts the table into a heap and then the new clustered index is applied. Because Azure does not support heaps (tables without clustered indexes) it is not possible to change the clustered index without dropping the table and recreating it. In Azure you can not specify a clustered index in any other place other than the table create statement.

Comments

[Chris Marisic]

Interesting, however I don't want a primary key.

[Buildstarted]

I just left it in there but that primary key isn't required. I assumed you already had a primary key though so to force it to be nonclustered.

[Chris Marisic]

The clustered index is actually for a nonunique column (hence why it's not just a PK). I tried taking off the unique keyword from the 2nd example and sql server doesn't seem to like that.

[Buildstarted]

The problem is the CONSTRAINT requires PRIMARY KEY or UNIQUE when creating a CLUSTERED column...not sure of a work around other than to use a separate statement. msdn.microsoft.com/en-us/library/ms174979.aspx

[Nitesh]

Link is no more relevant.

[Brain2000]

Remember, a clustered constraint/index is required only in AZURE SQL. Installed SQL does not have this requirement. I realize the reader did mention that this was for Azure, but this answer kind of makes it sound like it applies to all flavors of SQL.