SQL Server: Clustered Indexes

When creating a new table within SQL Server Management Studio Express (SSMSE), it will not have an index or a primary key by default.

A primary key is not mandatory, but from a performance point of view, it nearly always makes sense to create one. The Primary Key is a UNIQUE “Index” Value in a Database Table, such as the “Customer Code” in a Customer List (or the “Order Number” in an Order Register).  A table can have only one Primary Key.

To create a Primary Key within SSMSE, right click the desired field within the table and click “Set Primary Key”. If there are any issues saving the table design changes, follow the instructions here.

SQL Server 2008 001

Once a Primary Key has been created, that particular database field (column) cannot contain duplicate values.

SQL Server 2008 002

You will now notice that a new “Key” has been created for the table, along with a “Clustered Index”.

Clustered Index
A Clustered Index ensures that the data within the table is stored in exactly the same physical order as the index itself. A clustered index does not have to be unique, but it will be if it is associated with a primary key (as above).

By definition, there can only be ONE clustered index per table.

A table without a clustered index is called a “Heap”.

Non-Clustered Index
Since there can be only one clustered index per table, all other indexes must be Non-Clustered. These do not affect the way in which the data is physically stored.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s