Special Types of Indices

The Database Engine allows you to create the following special types of indices:

• Indexed views
• Filtered indices
• Indices on computed columns
• Partitioned indices
• Column store indices
• XML indices
• Full-text indices

Indexed views are based on views and therefore will be discussed in the next chapter.Filtered indices are similar to  indexed views. You can find their description in Books Online. Partitioned indices are used with partitioned tables and are described in Chapter “Optimizing Techniques for Relational Online Analytical Processing”. Column store indices are one of the most important new features in SQL Server 2012 and will also be explained in detail in Chapter “Optimizing Techniques for Relational Online Analytical Processing”. Indices in relation to XML are explained in detail in Chapter “SQL Server and XML”, while full-text indices are a topic of Chapter “SQL Server Full-Text Search”.

This section discusses computed columns and indices in relation to them.

A computed column is a column of a table that is used to store the result of a computation of the table’s data. Such a column can be either virtual or persistent. The following subsections describe these two forms of computed columns.

Virtual Computed Columns

A computed column without a corresponding clustered index is logical—that is, it is not physically stored on the hard disk. Hence, it is recomputed each time a row is accessed. Example 10.8 demonstrates the use of virtual computed columns.

USE sample;
CREATE TABLE orders
	(orderid INT NOT NULL,
	price MONEY NOT NULL,
	quantity INT NOT NULL,
	orderdate DATETIME NOT NULL,
	total AS price * quantity,
	shippeddate AS DATEADD (DAY, 7, orderdate));Code language: PHP (php)

The orders table in Example 10.8 has two virtual computed columns: total and shippeddate. The total column is computed using two other columns, price and quantity, while the shippeddate column is computed using the date function DATEADD and the column orderdate.

Persistent Computed Columns

The Database Engine allows you to build indices on deterministic computed columns, where the underlying columns have precise data types. (A computed column is called deterministic if the same values will always be returned for the same table data.)

An indexed computed column can be created only if the following options of the SET statement are set to ON. (These options guarantee the determinism of the column.)

• QUOTED_IDENTIFIER
• CONCAT_NULL_YIELDS_NULL
• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS

Also, the NUMERIC_ROUNDABORT option must be set to OFF.

If you create a clustered index on a computed column, the values of the column will physically exist in the corresponding table rows, because leaf pages of the clustered index contain data rows (see the “Clustered Indices” section earlier in this chapter). Example 10.9 shows the creation of a clustered index for the computed column total in Example 10.8.

CREATE CLUSTERED INDEX i1 ON orders (total);

After the execution of the CREATE INDEX statement in Example 10.9, the computed column total will physically exist. This means that all updates to the underlying columns that build the computed column will cause the modification of the computed column itself.