INDEXING CONSIDERATIONS - A Developer's Guide to Data Modeling for SQL Server - page 222

than one of each of these files, but it has at least one of each when it is cre-
ated. For the purpose of our discussion on indexes, we are particularly con-
cerned with the data file.
Inside the data file is a collection of objects known as extents, which
are also collections of objects known as pages. Pages are small (8K) allo-
cations of disk space that contain row data. Extents are collections of eight
pages. Each page generally contains its own identifier, along with rows of
data in the order they were inserted into the database. Every table in the
database is composed of these pages and extents. Another way to look at it
is that tables are designations of extents (and therefore pages) that contain
rows of data. It is this basic structure that we are concerned with when dis-
cussing indexes.
What Are Indexes?
By default, when you create a new table in a SQL Server database, the
server assigns a starting number of extents to that table. When you start in-
serting data, it adds rows of data to the pages inside the extents. Once a
page is full, it begins inserting data into the next available page in that ex-
tent. Once an extent is full, it assigns the next available extent to that table
and begins inserting data into the first page in the new extent.
Again, by default and without any extra design on your part, the server
sorts the rows on the pages in a first-come, first-served manner. This
means that unless you tell it otherwise, your data will be sorted based on
when it was inserted, period. A table that has been built this way is known
as a heap. You can think of it as being similar to a pile of laundry; every-
thing has been piled in heap. For a very small table, this might not be a
problem (it's easy to find one pair of jeans in a pile of 8 pairs). But for any-
thing with more than a few rows, it can start to be difficult to work with
(imagine finding that one pair in 64 piles of 8 jeans each!).
This is where indexes come in. Simply put, an index is a referencing
set of pointers to rows of data. Additionally, depending on the type of
index, it may actually sort the data, giving you faster access to the rows.
Indexes physically exist on disk, and thus they take up disk space separately
from, and in addition to, your actual table data. There are even special
types of pages that exist to manage indexes. We don't cover exactly how in-
dexes are managed on disk; just remember that you'll need to account for
them in the overall size of your database. We discuss that basic math for
calculating index sizes when we outline the types of indexes.