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

a row is deleted, the index needs to know that, too. So for every row in a
table that has a nonclustered index, some internal processing must occur
whenever the data changes.
Now let's look at the case of a clustered index. Once a clustered index
has been placed on a table, the data in that table has been physically or-
dered on its pages on disk to meet the clustering key requirements. In our
Customers example, this means that the data has been ordered alphabeti-
cally by last name. If a new record is added, that record must be inserted
into the correct place on disk, according to the value of the last name.
What if the page that contains the rows immediately before and after
the new row is full? We now must move the data to keep it in order, cor-
rect? Not quite (that would be very inefficient), but there is some process-
ing involved. When this situation occurs, SQL Server allocates a new page
to the object (hopefully from the same extent, if possible) and adjusts the
link list accordingly. This process takes some resources, however, and is not
to be taken lightly. When updates occur, as with a nonclustered index, this
processing overhead is incurred only if the update applies to an index key
value. Finally, deletions don't incur much overhead that isn't incurred by
the heap or nonclustered index.
You can see now that creating and maintaining indexes on a table are
not free operations. So even though indexes give you a tremendous per-
formance benefit on your data retrieval queries, you pay the price when in-
serting or updating the data. The thing to keep in mind is how the database
is used. SQL Server has highly efficient index maintenance processes, so
inserting records one or two at a time to a table that has an index is usually
efficient enough that the benefit of having the index for queries outweighs
the cost of maintaining the index.
For operational databases, such as the one we built for Mountain View
Music, having the indexes in place will give us a huge performance bene-
fit. The indexes will help the front-end application search for existing cus-
tomer records, search and display product listings, and generate billing
notices. This is because the database has a balanced read versus write
usage. We are regularly adding one or two rows to each table, and regularly
retrieving one or two rows from each table. It is conceivable that someone
would occasionally run a large query, such as to view a comprehensive list
of customers, but those queries would be rare, and a covering index would
likely ensure adequate performance of that query.
However, not all databases have the same usage. If your database is
write-heavy, you will need to carefully consider your index scheme. When