Like this article? We recommend

Appropriate indexes are a necessary part of any successful database
application. In fact, you can see performance improvements of 10, 100, and
sometimes even 1,000 fold by adding helpful indexes to your database tables.
This article introduces the basics of clustered and non-clustered indexes, and
gives you some ideas and tips for selecting a proper indexing strategy.

Indexes 101

Simply put, an index is a physical structure containing pointers to
the data. SQL Server 2000 supports up to 250 indexes per table, although
you'll generally have far fewer indexes on each table. You can't build
indexes on columns with certain datatypes such as TEXT, NTEXT,
and IMAGE.

NOTE

Indexes used for full-text search are very different from the regular
clustered/non-clustered indexes, and are not discussed in this article.

There are two types of indexes: clustered and non-clustered. SQL Server index
structure is often compared to the structure of a tree. Both clustered and
non-clustered indexes have two types of nodes: leaf nodes and root
nodes. The leaf level of the clustered index is the data itself. The leaf level
of a non-clustered index is a pointer to the root level of the clustered
index.

The clustered index keythat is, the column(s) on which the index is
builtdetermines how data is physically ordered. So if you build a
clustered index on state columns of the authors table in the
Pubs database, the data will be ordered based on the values of
statein either ascending or descending order. The CREATE
INDEX statement defaults to ascending order, but you can override it with
the DESCENDING keyword if necessary.

After reading the previous paragraph, it shouldn't come as a surprise
that you can have only one clustered index per tableyou can store
data ordered in only one way. Since the leaf level of the clustered index is the
data itself, reading data using the clustered index is much faster than when
using a non-clustered index. If you have a need for optimal performance, choose
the column that has the clustered index very carefully.

Clustered indexes must be unique; since the data is ordered according to the
values in the clustered index key, SQL Server must have a way to uniquely
identify the position of each data row in the index. If you have duplicate
values in the clustered key, SQL Server will add a hidden column (key) to the
index to make it unique.

Non-clustered indexes are not as efficient as clustered indexes; however,
scanning a non-clustered index can still be much more efficient than scanning an
entire table. Since you can have only one clustered index per table, all other
indexes you need must be non-clustered.