Clustered Versus Nonclustered Indexes

Many students ask which one is better—a clustered or nonclustered index? For many queries, a clustered index is more efficient than a nonclustered index on the same column(s). But only one clustered index can be created per table, so most tables have a combination of clustered and nonclustered indexes.

The following list helps to answer the question. Rather than ask “Which is better?”, it might be better to ask “What are their relevant strengths?”

•  Clustered indexes are best at range searches, where their efficiency at scanning larger quantities of data is most useful.

•  Clustered indexes are most likely to help with a query requesting sorted data.

•  Nonclustered indexes are almost as fast as clustered indexes at finding a single row of data.

•  Nonclustered indexes can cover a query. This means that SQL Server can use an index containing all the columns required to resolve the query independently, without accessing the table itself. A covered query often can run substantially faster than a query accessing a table, even with a clustered index.

A Brief Look at Optimization

Every time you execute a SQL batch, SQL Server runs through a four-step process:

1.  Parse. At this step, SQL Server checks the syntax and verifies that all referenced objects exist.

2.  Optimize. Now SQL Server chooses how to execute the query.

3.  Compile. At this stage, the server creates an executable component.

4.  Execute. Finally, SQL Server runs the compiled plan.

Now that we’ve looked at how SQL Server actually uses the different types of indexes when executing the query, the next step is to understand how SQL Server decides which indexes to use on a specific query. The process of choosing an access method for a query is called optimization.

Optimization is the process of selecting an access path for resolving a query.

---

Note:  This is by far the most difficult aspect of SQL Server for experienced programmers to grasp. SQL Server will decide when and where to use indexes to make queries more efficient. As we step through the decision process, you will see how that process is affected by current characteristics of the data, so it is far more flexible and efficient over time than you could be by deciding to write a certain program in a certain way.

Nevertheless, this is the part that’s hard to get used to. The server is seldom wrong about the most efficient path to retrieve the data, but the loss of control that comes with the efficiency leaves programmers struggling for something concrete.

---

SQL Server uses a cost-based optimizer, which means that the server focuses on the cost (in terms of physical resources—principally disk and memory reads) of performing a query using a certain approach.

The optimization process itself consists of several steps:

1.  Find search arguments (SARGs).

2.  Match SARGs to indexes.

3.  Estimate the work to resolve the query using each index (as well as without an index).

4.  Choose the most efficient way to resolve the query.

We’ll walk through this process ourselves for a sample query. Here’s the query:

select count(*) “count”, sum(qty) “total sold”
from sales
where stor_id between “F” and “Gz”
and title_id like “BI2%”

Step 1: Find SARGs

SQL Server first tries to find elements of a query that will allow the server to use an index— search arguments, or SARGs.

A SARG is a portion of a WHERE clause that is in the form

column-name comparison-operator constant-expression

as in

title_id like “BI2%”

or

qty > 500

Only SARGs (or so-called “improvised” SARGs) can take advantage of indexes. So the first step in improving your queries is to make certain that the server is able to recognize SARGs in your WHERE clauses.

In the case of our example, there are two SARGs: the BETWEEN clause for stor_id and the LIKE test for title_id.

Step 2: Match SARGs to Indexes

At this stage, SQL Server looks for indexes that might be useful for the query. Each column referenced by a SARG might have one or two relevant indexes (or more). The server also identifies composite indexes that might be useful.

---

Note:  The optimizer only uses an index if one of the search arguments is the FIRST column in the index. Many programmers expect that an index will be effective for a query because a column is included in an index, but it’s not true unless that column is the FIRST column.

---

In this case, SQL Server finds two candidate indexes, one for each SARG.