SQL Server equivalent of Oracle USING INDEX clause

Question

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.

• user1822: Just define the PK together with the table. create table c (c1 int not null primary key, c2 int)
• nik: It's the "making a PK use a named index" part that I'm interested in.
• Aaron: No there is no way to do that. A primary key is a constraint and an index. In what you've provided the primary key would by default be a unique clustered index. Which would make the other defined index a duplicate unless you included both c1 and c2 in the where clause.

Answer 1

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)

Answer 2

The SQL Server syntax for creating a clustered index that is also a primary key is:

CREATE TABLE dbo.c
(
 c1 INT NOT NULL, 
 c2 INT NOT NULL,
 CONSTRAINT PK_c
 PRIMARY KEY CLUSTERED (c1, c2)
);

As far as your comment: "making a PK use a named index", the above code will result in the primary key index being named "PK_c".

The primary key and the clustering key do not have to be the same columns. You can define them separately. In the above example, change the CLUSTERED keyword to NONCLUSTERED, and then simply add a clustered index using the CREATE INDEX syntax:

CREATE TABLE dbo.c
(
 c1 INT,
 c2 INT,
 CONSTRAINT PK_c
 PRIMARY KEY NONCLUSTERED (c1, c2)
);
CREATE CLUSTERED INDEX CX_c ON dbo.c (c2);

In SQL Server the clustered index is the table, they are one-and-the-same. A clustered index defines the logical order of the rows stored in the table. In my first example, rows are stored in the order of the values of the c1 and c2 columns. Since the clustering key is also defined as the primary key, the combination of c1 and c2 must be unique table-wide.

In the second example, the primary key is composed of the c1 and c2 columns, however the clustering key is just the c2 column. Since I did not specify the UNIQUE attribute in the CREATE INDEX statement, the clustering key (c2) is not required to be unique across the table. A "uniquifier" will be automatically created by SQL Server and appended to the values in the c2 column to create the clustering key. This clustering key, since it is now unique, will then be used as a row id in other indexes created on the table.

In order to prove the clustering key controls the layout of rows in storage, you can use the undocumented function, fn_PhysLocCracker(%%PHYSLOC%%). The following code shows the rows are laid out on disk in order of the c2 column, which I've defined as the clustering key:

USE tempdb;
CREATE TABLE dbo.PKTest
(
 c1 INT NOT NULL
 , c2 INT NOT NULL
 , c3 VARCHAR(256) NOT NULL
);
ALTER TABLE PKTest 
ADD CONSTRAINT PK_PKTest 
PRIMARY KEY NONCLUSTERED (c1, c2);
CREATE CLUSTERED INDEX CX_PKTest 
ON dbo.PKTest(c2);
TRUNCATE TABLE dbo.PKTest;
INSERT INTO dbo.PKTest (c1, c2, c3)
SELECT TOP(25) o1.object_id / o2.object_id, o2.object_id, o1.name + '.' + o2.name
FROM sys.objects o1
 , sys.objects o2
WHERE o1.object_id >0 
 and o2.object_id > 0;
SELECT plc.file_id
 , plc.page_id
 , plc.slot_id
 , pk.*
FROM dbo.PKTest pk
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc;

The results from my tempdb, are:

enter image description here

In the image above, the first three columns are output from the fn_PhysLocCracker function, showing the physical ordering of rows on disk. You can see the slot_id value increases lock-step with the c2 value, which is the clustering key. The primary key index stores rows in a different order, which can be seen by forcing SQL Server to return results from scanning the primary key:

SELECT pkt.c1
 , pkt.c2
FROM dbo.PKTest pkt WITH (INDEX = PK_PKTest, FORCESCAN);

Note, I didn't use an ORDER BY clause in the above statement since I am attempting to show the order of items in the primary key index.

The output from the above query is:

enter image description here

Looking to the fn_PhysLocCracker function, we can see the physical order of the primary key index.

SELECT plc.file_id
 , plc.page_id
 , plc.slot_id
 , pkt.c1
 , pkt.c2
FROM dbo.PKTest pkt WITH (INDEX = PK_PKTest, FORCESCAN)
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc;

Since we are exclusively reading from the index itself, i.e. no columns outside the index are being referenced in the query, the %%PHYSLOC%% values represent the pages in the index itself.

The results:

enter image description here