Sql-server – SQL Server equivalent of Oracle USING INDEX clause

constraintoracleprimary-keysql serversql-server-2008

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle?
Specifically for the construct:

CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

In the Sql Server Documentation on Unique Indexes it states (emphasis added):

Unique indexes are implemented in the following ways:

PRIMARY KEY or UNIQUE constraint

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

Which seems to imply that there is a way of specifying what index should be used for a Primary Key.

Best Answer

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle?

No. When you create a primary key or unique constraint in SQL Server, a unique index to support that constraint is created automatically, with the same keys.

Which seems to imply that there is a way of specifying what index should be used for a Primary Key.

No. The documentation is only attempting to explain whether the automatic supporting index will be created as clustered or nonclustered, if you do not specify. It is confusingly worded, I agree.

To clarify, when you add a primary key constraint to an existing table without expressing a preference, the supporting index will be clustered if there is no pre-existing clustered index on the table. The supporting index will be created as nonclustered if there is already a clustered index

You can specifically request a clustered or nonclustered primary key using: PRIMARY KEY CLUSTERED or PRIMARY KEY NONCLUSTERED.

In fairness, the documentation is much clearer on the subject at:

table_constraint (Transact-SQL)

Related Question