Guide to SQL Server Table Indexes – Part 1
of the most important tasks for every database administrator (DBA) is to ensure
that query times are consistent with service-level agreement (SLAs) or within
user expectations. One of the most effective techniques to achieve this
objective is to create indexes on tables for your queries. SQL Server uses
indexes to sort and organize table data. It creates indexes based on ordering
values from one or more selected columns. SQL Server automatically creates
indexes when you define a primary key or unique constraint. You can use indexes
to manage and track rows in a table or an indexed view.
improve the performance on most data scans by reducing the overall time query
takes to run and the amount of work and resources it consumes. The amount of performance
improvement depends on the size of the tables involved, the index design, and
the type of query. You can see the role of indexes by observing what happens
during queries and data manipulation.
this part, you will learn about the basics of SQL Server database table’s
indexes, the difference between the clustered and non-clustered indexes, and
how the leaf nodes, non-leaf nodes, and heaps associated with data storage.
table that has no clustered index is stored in a set of data pages called a heap.
The data is usually stored initially in the order in which the rows are
inserted into the table, but SQL Server Database Engine moves the data around
the heap to store the rows efficiently, so the order of rows cannot be
predicted and are in no particular order, similarly data pages are not sequenced
in a particular order. The only way to guarantee the order of the rows from a
heap is to use ORDER
BY clause of Transact-SQL.
without an index
you access data, SQL Server first determines if there is an index available. If
not, the database engine retrieves data by scanning the entire table. The
database engine begins scanning at the physical beginning of the table, and scans
through the full table page-by-page and row-by-row to look for qualifying data.
Then extracts and returns rows meeting the selection criteria.
with an index
process is improved when indexes are present. If there is an appropriate index available,
SQL Server uses it to locate the data. An index improves the search process by
sorting data on key columns. The database engine begins scanning at the first
page of the index, and only scans those pages that potentially contain
qualifying data, based on the index structure and key columns. Finally retrieves
the data rows or pointers that contain the locations of the data rows to allow
direct row retrieval.
information SQL Server returns from the table scan, rows or pointers, depends
on whether the database engine scans a clustered or non-clustered index:
SQL Server physically sorts table rows in index order based on key column
values. The scan returns the actual data rows.
The index doesn’t affect physical sort order. The scan returns pointers to the
An index is made up of nonleaf nodes
and leaf nodes, and is structured as B-Trees, as shown in figure below:
nodes are hierarchical nodes that make up the index sort tree.
nodes are also referred to as intermediary nodes.
nodes point to other nonleaf nodes one step down in the hierarchy until reaching
the leaf nodes.
nodes are at the bottom of the hierarchy.
a clustered index, the leaf nodes are data pages, containing table rows.
In a non-clustered index the leaf
nodes contain pointers to the data rows.
The non-clustered indexes are based
on clustered indexes when present. The leaf node of a non-clustered index points
to leaf node locations in clustered indexes. When you rebuild a clustered
index, the leaf node structure of non-clustered indexes also changes. Similarly
any changes or modification to the clustered index changes the non-clustered
Indexes are most effective when you
need a small percentage of the records stored in the table and there is a high
level of selectivity between the elements (unique or nearly unique values). The
main reasons for the creation of indexes are:
performance when sorting or grouping data.
performance on queries that join tables.
uniqueness on a column or set of columns.
You won’t typically create indexes
on columns that have few unique values or on columns that are seldom referenced
A table can have:
or one clustered index.
to 249 non-clustered indexes.
An index is
based on one or more key columns. When you define an index:
can specify no more than 16 key columns.
index row length (the total of all key columns) cannot exceed 900 bytes.
data types present a possible concern. If the current data in a variable length
data type does not exceed the 900-byte limit, you can create an index with variable-length
data types that have a potential of exceeding this limit. For example, you specify
the following columns:
Current maximum in use
create an index based on these key columns, but SQL Server returns a warning that
errors can occur in the future. Consider a situation in which, at some future
time, you attempt to add data that uses the maximum length of the nvarchar
data types. When you do, the operation will fail. This is because SQL Server
cannot create a new row in the index because the total physical length, the
space required to store the data, exceeds the 900-byte limit.
some drawbacks to imposing indexes on a table:
indexes takes time and disk space.
update queries (INSERT, UPDATE, or DELETE) must also update table indexes.
tables generally do not receive measurable benefits from being indexed.
columns cannot include bit, text, ntext, or image type data columns.
You use the
INSERT, UPDATE, and DELETE statements to modify table data. When you add data
(INSERT), change data values (UPDATE), or remove rows from a table (DELETE),
SQL Server updates any indexes on that table. The more indexes SQL Server has
to update, the more overhead that is incurred by the process.
In the next
part, you will learn about the different types of indexes available in SQL
Server, and what are these indexes advantages and dis-advantages.