Database Reference
In-Depth Information
Special Index Types
As special data types and storage mechanisms are introduced to SQL Server by Microsoft, methods for indexing these
special storage types are also developed. Explaining all the details possible for each of these special index types is
outside the scope of the topic. In the following sections, I introduce the basic concepts of each index type in order to
facilitate the possibility of their use in tuning your queries.
Full-Text
You can store large amounts of text in SQL Server by using the
MAX
value in the
VARCHAR
,
NVARCHAR
,
CHAR
, and
NCHAR
fields. A normal clustered or nonclustered index against these large fields would be unsupportable because a single
value can far exceed the page size within an index. So, a different mechanism of indexing text is to use the full-text
engine, which must be running to work with full-text indexes. You can also build a full-text index on
VARBINARY
data.
You need to have one column on the table that is unique. The best candidates for performance are integers:
INT
or
BIGINT
. This column is then used along with the word to identify which row within the table it belongs to, as well as
its location within the field. SQL Server allows for incremental changes, either change tracking or time-based, to the
full-text indexes as well as complete rebuilds.
SQL Server 2012 introduces another method for working with text called Semantic Search. It uses phrases from
documents to identify relationships between different sets of text stored within the database.
Spatial
Introduced in SQL Server 2008 is the ability to store spatial data. This data can be either a geometry type or the
very complex geographical type, literally identifying a point on the earth. To say the least, indexing this type of data
is complicated. SQL Server stores these indexes in a flat B-tree, similar to regular indexes, except that it is also a
hierarchy of four grids linked together. Each of the grids can be given a density of low, medium, or high, outlining how
big each grid is. There are mechanisms to support indexing of the spatial data types so that different types of queries,
such as finding when one object is within the boundaries or near another object, can benefit from performance
increases inherent in indexing.
A spatial index can be created only against a column of type
geometry
or
geography
. It has to be on a base table,
it must have no indexed views, and the table must have a primary key. You can create up to 249 spatial indexes on any
given column on a table. Different indexes are used to define different types of index behavior. More information is
available in the topic
Beginning Spatial with SQL Server 2008
by Alastair Aitchison (Apress, 2009).
XML
Introduced as a data type in SQL Server 2005, XML can be stored not as text but as well-formed XML data within SQL
Server. This data can be queried using the XQuery language as supported by SQL Server. To enhance the performance
capabilities, a special set of indexes has been defined. An XML column can have one primary and several secondary
indexes. The primary XML shreds the properties, attributes, and elements of the XML data and stores it as an internal
table. There must be a primary key on the table, and that primary key must be clustered in order to create an XML
index. After the XML index is created, the secondary indexes can be created. These indexes have types
Path
,
Value
,
and
Property
, depending on how you query the XML. For more details, check out
Pro SQL Server 2008 XML
by
Michael Coles (Apress, 2008).
