Why does SQL Server use a non-clustered primary key?


Indexes are important in your database design. In Microsoft SQL Server, the default when you create a primary key is for it to be a clustered index. However, there are times when the primary key isn’t the best choice for a clustered index. For instance, in specialized situations where bulk insert performance matters more than lookup performance (especially if there aren’t going to be many rows to look up), it may make sense to leave the table as a “heap” (i.e., a table without any clustered index). Also, queries may in some cases be more likely to search indexes other than the primary key (for example, if the primary key is an identity column and your WHERE clauses are targeting more content-oriented data such as names and dates).

In addition, sometimes searches can be improved by using a clustered index that starts with a column that contains a broad category (e.g., a low-selectivity column, meaning that many values repeat). For instance, a foreign key to a country ID could be the first column in a clustered index, so that all records belonging to a given country would be found together. This could speed up searches where country is an important criterion. However, such an index would make little sense as a primary key if the country designation doesn’t play any role in determining uniqueness. Finally, in some circumstances, the values used for the primary key may change frequently. Such changes are much more costly when the index is clustered.

You can get additional information on indexes here.

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 )

Google+ photo

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

Connecting to %s