CODEWITHSUNDEEP
sql-serverindexing
DahnChaser
DahnChaser

Reputation: 15

Primary key indexes

Is searching a primary key column in a table faster than searching a non primary key column due to the default clustered index on primary keys in SQL Server?

Upvotes: 1

Views: 149

Answers (4)

Dan Guzman
Dan Guzman

Reputation: 46425

Is searching a primary key column in a table faster than searching a non primary key column due to the default clustered index on primary keys in SQL Server?

Since the optimizer doesn't care whether an index supports a constraint or not, I'll paraphrase the question as:

Is searching using the clustered index key column(s) faster than searching via a non-clustered index key column(s)?

A clustered index seek a disk-based table is the fastest method to return the entire row 1) for singleton lookups and 2) for key range searches. This minimizes the number of logical reads need to locate and retrieve rows.

SQL Server uses clustered as the default for primary key indexes to leverage this performance benefit and encourage the practice that all tables should have a clustered index. Also, since the clustered index key is implicitly included in all non-clustered indexes as the row locator, the likelihood that non-clustered indexes cover queries is increased.

This is not to say that the primary key should always be the clustered index. There may be a better choice when queries most often use other columns in join/where clause predicates.

Upvotes: 0

Dragonthoughts
Dragonthoughts

Reputation: 2224

In general a relational database will be optimised to use primary keys efficiently, however your database structure, your query and which database engine on what platform will all be major factors in the performance.

Additionally, if the non-primary key column is not indexed, it will be orders of magnitude slower, regardless of your query.

Upvotes: 0

James Cooke
James Cooke

Reputation: 1293

from a previous question; "The reason we specify keys for a table is primarily to improve the data integrity and usefulness of the data. Keys guarantee the table is free from duplicate data and therefore they allow the user/consumer of the data to identify information correctly. DBMS query optimizers and storage engines are designed to take advantage of keys so having a key will also give your DBMS the best chance of executing some queries efficiently but there's no guarantee that adding a key will improve performance in every case"

That being said the existence of an Index should make searching faster in most cases, but is unrelated to the key per-se

Upvotes: 2

Mureinik
Mureinik

Reputation: 312344

Searching according to an index is generally faster than searching without an index (unless your table is extremely small, or the index is in a terrible condition). The fact that this index also supports a primary key is inconsequential to this discussion.

Upvotes: 0

Related Questions