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.

Answer 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.

Answer 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]