Database Reference
In-Depth Information
•
Like a book's Index architecture
: An ordered list can be created without altering the layout
of the table, similar to the way the index of a book is created. Just like the keyword index of a
book lists the keywords in a separate section with a page number to refer to the main content
of the topic, the list of
StandardCost
values is created as a separate structure and refers to the
corresponding row in the
Product
table through a pointer. For the example, I'll use
RowNumber
as the pointer. Table
8-1
shows the structure of the manufacturer index.
Table 8-1.
Structure of the Manufacturer Index
StandardCost
RowNumber
2171.2942
125
2171.2942
126
2171.2942
127
2171.2942
128
2171.2942
129
1912.1544
170
SQL Server can scan the manufacturer index to find rows where
StandardCost
is greater than 150. Since the
StandardCost
values are arranged in a sorted order, SQL Server can stop scanning as soon as it encounters the
row with a value of 150 or less. This type of index is called a
nonclustered index
, and I explain it in detail later in
the chapter.
In either case, SQL Server will be able to find all the products where
StandardCost
is greater than 150 more
quickly than without an index under most circumstances.
You can create indexes on either a single column (as described previously) or a combination of columns in
a table. SQL Server automatically creates indexes for certain types of constraints (for example,
PRIMARY KEY
and
UNIQUE
constraints).
The Benefit of Indexes
SQL Server has to be able to find data, even when no index is present on a table. When no clustered index is present to
establish a storage order for the data, the storage engine will simply read through the entire table to find what it needs.
A table without a clustered index is called a
heap table.
A heap is just an unordered stack of data with a row identifier
as a pointer to the storage location. This data is not ordered or searchable except by walking through the data, row
by row, in a process called a
scan.
When a clustered index is placed on a table, the key values of the index establish
an order for the data. Further, with a clustered index, the data is stored with the index so that the data itself is now
ordered. When a clustered index is present, the pointer on the nonclustered index consists of the values that define
the clustered index key. This is a big part of what makes clustered indexes so important.
Data within SQL Server is stored on a page, which is 8KB in size. A page is the minimum amount of information
that moves off the disk and into memory, so how much you can store on a page becomes important. Since a page has
a limited amount of space, it can store a larger number of rows if the rows contain a fewer number of columns or the
columns are of smaller size. The nonclustered index usually doesn't (and shouldn't) contain all the columns of the
table; it usually contains only a limited number of the columns. Therefore, a page will be able to store more rows of
a nonclustered index than rows of the table itself, which contains all the columns. Consequently, SQL Server will be
able to read more values for a column from a page representing a nonclustered index on the column than from a page
representing the table that contains the column.
