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

root levels. This technique helps minimize the size of the index, while still
allowing the query engine to reference those columns when searching
data. Consider using this feature when you're building indexes to satisfy
very specific queries and the index has gotten too large.
XML Indexes
Because SQL Server supports XML as a data type, it is necessary to in-
clude a special type of index that can assist with query performance when
you're searching large XML values. These indexes are fairly complicated,
allowing for multiple levels of indexing, but it is enough to know that they
assist in shredding (the process of taking data from an XML format to a re-
lational format) the XML binary large object (BLOB) by storing rows of
data in special storage structures that match each node in the BLOB. In
this way, the query engine can reference these special structures to look for
the disk location of specific pieces of the XML BLOB, thereby eliminating
a full scan of the BLOB and improving query performance.
Spatial
The spatial data types are new to SQL Server 2008, and the introduction
of the new data types required the addition of a new kind of index. Spatial
indexes are built on B-trees, but their purpose is to break down the data
into a two-dimensional space, allowing the data points to be indexed and
searched efficiently.
Full-Text Indexes
SQL Server provides a special type of index for the full-text engine (FTE)
that is built in to SQL Server. (If you're curious, its full name is Microsoft
Full-Text Engine for SQL Server [MSFTESQL].) A full-text index pro-
vides support for the highly specialized and sophisticated word searches in-
side character data that FTE is designed for. These indexes are actually
built on a token-based structure as opposed to a B-tree; they are beyond
the scope of our discussion. Just be aware that if you decide to use FTE,
you'll need to do some extra research on full-text indexes.
Indexed Views
Technically, an indexed view is not a type of index but an on-disk struc-
ture. However, in the context of understanding indexes, this is as good a
time as any to discuss it. Although views are compiled queries that simply
retrieve data sets, the intent is for them to behave like tables. For this
reason, SQL Server lets you place an index on them to enhance search