Database Reference
In-Depth Information
performance. When you index a view, you must use a clustered index,
which means that the view is materialized. This means that the underlying
query is first executed, and then the index is applied to the result set, and
the result set is then stored on disk. Therefore, an indexed view is actually
a copy of the data from the underlying tables. Keep this in mind when you
index views, because you are actually increasing the amount of data being
stored on disk, not only because of the index overhead but also because
you are duplicating data.
Once you understand the types of indexes available, you can begin to de-
termine exactly which indexes to use. This means going back to your re-
quirements and looking at your notes about how the database will be used.
The key to understanding indexes is to understand the queries that will be
run against the data. This means you'll need to talk, probably at length,
with the application developer to understand how the application will in-
teract with the data. Additionally, you'll need to account for the other uses
of the database: ad hoc queries, reporting, and the like. Knowing the pri-
mary use of the database (versus secondary uses) will also help you deter-
mine where to cluster your indexes and where to create covering indexes.
Reads versus Writes
Earlier we mentioned that there is a cost of doing all this index business.
Here's where it comes into play. When you have a heap, inserting rows into
a table is a matter of appending the new row of data to the end of the most
recently allocated page. Updating an existing row is a matter of updating
the value being changed, something that happens in place on the page
where the row exists. And deleting a row means removing the row from the
page (there are internal processes for cleaning up the unused space, but we
won't go there).
Suppose you've put a nonclustered index on a heap. Now you have a
separate object that constantly needs to be aware of where the rows are.
Every time a new row is inserted, the nodes of the B-tree in the referen-
cing index must be updated to reflect the existence, and location, of the
new row. If a row is updated, the index needs to be updated only if there
was a change in the value of the index key field of the given row. Finally, if
Search WWH ::
Custom Search