Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 360

❑ All data types except text, ntext, and image can be used as included columns. Varchar(max) ,

nvarchar(max) , varbinary(max) , and XML data types are not allowed to be index keys, but you
can include the data from these types in an index by using the include feature.

❑ It reduces the size of the index tree. By only having lookup columns in your index key and cover-

ing index columns as included columns, you reduce the size of the non-leaf levels, which makes
maintaining the index more efficient.

❑ In the previous example you could create the non-clustered index like this:

CREATE NONCLUSTERED INDEX idx_lastname
ON people (lastname)
INCLUDE (firstname)

to include the firstname at the leaf level of the index without making it an indexed column. Any
query using this index would find peopleID,lastname,firstname at the leaf level of the index.

DisablingIndexes
The ability to disable an index was introduced in SQL Server 2005 to meet the needs of customers who
required indexes to be created periodically for short-term needs. For example, a report may gain huge
benefits from a particular index, but the business only needs it once per year. You don't want the over-
head of managing an index that's used once per year, so you can disable it for most the year and just
enable it when you need to.

Disabling an index effectively drops it and just keeps the definition, so you don't need to remember the
details of the indexes that need to be re-created when it comes to run the report; just enable the ones
already there. Enabling an index is the same as creating it from scratch; you just don't need to specify the
structure.

We've also seen customers using this in data load scenarios where they disable the indexes beforehand
and enable them again after the load to save time dropping and recreating. It's only really useful for
non-clustered indexes, though, as disabling a clustered index prevents access to the underlying data.

You disable and enable indexes using the ALTER INDEX command:

ALTER INDEX idx_lastname
ON dbo.people
DISABLE
ALTER INDEX idx_lastname
ON dbo.people
REBUILD

Statistics
Statistics are used by the optimizer to help it decide on the most efficient way to execute a query. They
are created for every index. Internally, SQL Server builds a histogram using up to 200 values from your
index and calculates the number of rows that fall between each value (the gap between each value is
called an interval) and the density of values within an interval. Density is a measure of the number of
duplicate values. Armed with this information the optimizer can choose whether or not to use particular
indexes without having to read the data. Basic information on all the statistics on a table can be viewed