SQL Server Index Myth 2: Primary Keys Are Always Clustered

The next myth that is quite prevalent is the idea that primary keys are always clustered. While this is true in many cases, you cannot assume that all primary keys are also clustered indexes. Earlier in this book, I discussed how a table can have only a single clustered index on it. If a primary key is created after the clustered index is built, then the primary key will be created as a nonclustered index.

To illustrate the indexing behavior of primary keys, I’ll use another demonstration that includes building two tables. On the first table, named dbo.MythTwo1, I’ll build the table and then create a primary key on the RowID column. For the second table, named dbo.MythTwo2, after the table is created, the script will build a clustered index before creating the primary key. The code for this is in Listing 5.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: