An index can be defined using one or more columns in the table, the index key, with the following restrictions:
The column(s) defined for the clustered index are referred to as the clustering key. A clustered index is special because it causes SQL Server to order the data in the table according to the clustering key. Because a table cannot be sorted more than one way, you can define only one clustered index on a table.
Note
 |
A table without a clustered index is referred to as a heap. As a general rule of thumb, every table should have a clustered index. One of the primary reasons for creating a clustered index on each table is to eliminate forwarding pointers. Forwarding pointers are well beyond the scope of this book. To learn more about forwarding pointers and the detrimental effects on query performance, please refer to Inside SQL Server 2005: Query Tuning and Optimization by Kalen Delaney.
|
The general syntax for creating a relational index is as follows:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON
You may recall the table creation scripts that we used in Article, “Designing Tables,” had a keyword of “clustered” in the specification of a primary key. Although a primary key is a constraint, SQL Server physically implements a primary key as an index. Because the default option for a primary key is clustered, SQL Server creates a clustered index for a primary key. Likewise, a unique constraint is physically implemented as a unique index. Since a primary key is also unique, by default, SQL Server physically implements each primary key as a unique, clustered index.
Note
|
Partition schemes will be discussed in article about Partitioning.
|
In the practice that follows, you will create clustered indexes on the OrderHeader and OrderDetail tables previously created in article "Designing Tables". Since we have already created primary keys on the OrderHeader and OrderDetail tables using the default options, you will first drop and re-create the primary keys as nonclustered in order to create the single, allowed clustered index on the table.
Create a Clustered Index
1. |
Execute the following code against the SQL2008SBS database:
-Must drop foreign key first, because it depends on the primary keyALTER TABLE Orders.
OrderDetail
DROP CONSTRAINT fk_orderheadertoorderdeatils
GO
-Change the existing primary keys to nonclustered
ALTER TABLE Orders.OrderHeader
DROP CONSTRAINT pk_orderheader
GO
ALTER TABLE Orders.OrderHeader
ADD CONSTRAINT pk_orderheader PRIMARY KEY NONCLUSTERED (OrderID)
GO
ALTER TABLE Orders.OrderDetail
DROP CONSTRAINT pk_orderdetail
GO
ALTER TABLE Orders.OrderDetail
ADD CONSTRAINT pk_orderdetail PRIMARY KEY NONCLUSTERED (OrderDetailID)
GO
-Recreate the foreign key (fix the misspelling from article "Designing Tables")
ALTER TABLE Orders.OrderDetail
ADD CONSTRAINT fk_orderheadertoorderdetails FOREIGN KEY (OrderID)
REFERENCES Orders.OrderHeader (OrderID)
GO
-Create new clustered indexes on the ShipDate/FinalShipDate columns
CREATE CLUSTERED INDEX icx_finalshipdate ON Orders.OrderHeader (FinalShipDate)
GO
CREATE CLUSTERED INDEX icx_shipdate ON Orders.OrderDetail (ShipDate)
GO
|
2. |
In the Object Explorer, expand the Indexes node for the OrderHeader and OrderDetail tables. Observe that you now have a clustered index along with a unique, nonclustered index created against the table. |
