INDEXING IN SQL SERVER

Using the Right Indexes for Optimal Performance Query optimization is a complex game with its own rules.

Let’s look at three examples to discover when SQL Server Query Optimizer uses clustered indexes and non-clustered indexes to retrieve data and when to use the primary key (PK) to influence performance.

Example 1: Default Index Usage

Let’s look first at Query Optimizer’s default use of indexes.

Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (relative to the batch).

In this example, no query hint is specified, so Query Optimizer can use any index it wants to use, which results in optimal performance.

Notice that even though ContactID (the primary key [PK] of the Contact table) is retrieved in Query 1, Query Optimizer does not use a primary key clustered index; instead, it uses a non-clustered index. In Query 2, on the other hand, Query Optimizer uses a clustered index on PK where all columns (*) are retrieved.

This may be surprising. It is a common belief that when PK columns are used and no other condition or joins are used, Query Optimizer will use a PK clustered index to return the results of a SELECT statement. However, this is not always true.

Example 2 : Forcing a Primary Key (PK) Clustered Index

Let’s see how performance changes when a primary key clustered index is used to retrieve data.

Query 1’s query cost (relative to the batch) is equals to Query 2’s query cost (also relative to the batch).

In this example, we are using a primary key clustered index to retrieve data. The same execution plan is created whether we retrieve only one column or all the columns.

Example 3: Forcing Non-Clustered Index

Now consider two queries in which a non-clustered index is used to retrieve data.

Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (also relative to the batch).

In this example, we are using a non-clustered index to retrieve data. From the execution plan, it is very clear that retrieving only one column is much faster than retrieving all the columns from the Contact table. When all the columns from a table are selected, a PK clustered index is clearly the best option.