1008
Part VII: Performance Tuning and Optimization
is the table. All the data for the table is stored in the leaf pages of the clustered index.
Because you can sort a table only in one physical sorted order, you can have only one clus-
tered index per table.The keys defi ned for a clustered index must always be unique. When you defi ne a clustered index without using the
UNIQUE keyword, a “uniquifi er” is added to the clustered key to ensure the set of values is unique. This is an integer
value that makes your key larger. Because the clustered key is always stored in nonclustered indexes, it makes these
indexes larger as well. Keep this in mind when defi ning your clustered index keys.Logically, the clustered index pages maintain the data in the clustered index sort order.
However, physically those pages are connected via a linked list — each page links to the
next page and previous page in the list, so it is not guaranteed that the data remains in a
sorted physical order. Also, it is not guaranteed that the rows on a page are stored in sorted
order. An offset array is used at the end of the page to indicate where in the page rows
begin and end in a sorted fashion. In a perfect world the pages would be in the same order
as the list, but in reality they are often moved around due to page splits and fragmentation
(more on page splits later in this chapter).A great example to illustrate a clustered index is a telephone book. A telephone book is
always in sorted order, based on the last name of the individual followed by the fi rst name.
The sorted order makes it easy to fi nd the phone number of the person you’re looking for.Assume you want to fi nd my telephone number in the book. To do this, you’d base your
search primarily on my last name, Chapman. You’d open the phone book approximately in
the middle to gauge how close you were to the “Chapman” section. You’d then pick the side
that contained C and split it again. You’d continue this operation until you fi nd the page
that contains the last name Chapman, and it would take you only a few operations to do
this. When you fi nd my last name, you could then perform a search for my fi rst name (Tim)
within the Chapman result set. When you fi nd my full name, you’d then have access to my
address information without needing to perform any additional searches. What you just
performed to fi nd my last name was a type of binary search operation. You split the prob-
lem in halves until you reached the solution.This is similar to an index search operation. When searching for a value, you begin at the
root page (approximately the “middle” of the values you’re searching for) and continue
down intermediate paths, choosing the proper path for the value you’re searching for.
Eventually you make it to the leaf level of the index, where you fi nd the value you’re look-
ing for. For clustered indexes, after you make it to the leaf level, all the data you need is
contained there, so there is no need to perform any additional searching on the data. All
the data in the table is contained at the leaf level of the index; this means that the clus-
tered index is the table.c45.indd 1008c45.indd 1008 7/31/2012 10:16:38 AM7/31/2012 10:16:38 AM
http://www.it-ebooks.info