Index Analysis - SQL Server Query Performance Tuning - page 156

So, although one index is taking up radically less room on approximately one-quarter as many pages, it's done
at no cost in performance.
Compression has a series of impacts on other processes within SQL Server, so further understanding of the possible
impacts as well as the possible benefits should be explored thoroughly prior to implementation. In most cases, the cost
to the CPU is completely outweighed by the benefits everywhere else, but you should test and monitor your system.
Clean up the indexes after you finish testing.

DROP INDEX Person.Address.IX_Test;
DROP INDEX Person.Address.IX_Comp_Test;
DROP INDEX Person.Address.IX_Comp_Page_Test;

Columnstore Indexes
Introduced in SQL Server 2012, the columnstore index is used to index information by columns rather than by rows.
This is especially useful when working within data warehousing systems where large amounts of data have to be
aggregated and accessed quickly. The information stored within a columnstore index is grouped on each column, and
these groupings are stored individually. This makes aggregations on different sets of columns extremely fast since the
columnstore index can be accessed rather than accessing large numbers of rows in order to aggregate the information.
Further, you get more speed because the storage is column oriented, so you'll be touching storage only for the
columns you're interested in, not the entire row of columns. Finally, you'll see some performance enhancements
from columnstore because the columnar data is stored compressed. The columnstore comes in two types, similar to
regular indexes: a clustered columnstore and a nonclustered columnstore. The nonclustered column store cannot
be updated. You must drop it and then re-create it (or, if you're using partitioning, you can switch in and out different
partitions). A clustered column store was introduced in SQL Server 2014 and is available there and only in the
Enterprise version for production machines. There are a number of limits on using columnstore indexes.

binary , text , varchar(max) , uniqueidentifier
(in SQL Server 2012, this data type works in SQL Server 2014), clr data types, xml , or decimal
with a precision greater than 18.

• You can't use certain data types such as

• You can't create a columnstore index on a sparse column.

• When creating a clustered columnstore, it can be the only index on the table.

• A table on which you want to create a clustered columnstore can't have any constraints

including primary key or foreign key constraints.

For the complete list of restrictions, refer to Books Online.
Columnstores are primarily meant for use within data warehouses and therefore work best when dealing
with the associated styles of storage such as star schemas. In the AdventureWorks2012 database, the
Production.TransactionHistoryArchive table is a structure that is more likely to be used for aggregate queries than
many of the other structures. Since it's an archive table, the load of it is also controlled so that a columnstore index
could be used successfully here. Take this query as an example:

SELECT tha.ProductID,
COUNT(tha.ProductID) AS CountProductID,
SUM(tha.Quantity) AS SumQuantity,
AVG(tha.ActualCost) AS AvgActualCost
FROM Production.TransactionHistoryArchive AS tha
GROUP BY tha.ProductID;