Index Analysis - SQL Server Query Performance Tuning - page 154

Figure 9-12. Execution plan with the indexed view automatically used
Even though the queries are not modified to refer to the new indexed view, the optimizer still uses the indexed
view to improve performance. Thus, even existing queries in the database application can benefit from new indexed
views without any modifications to the queries. If you do need different aggregations than what the indexed view
offers, you'll be out of luck. Here again the columnstore index shines.
Make sure to clean up.
DROP VIEW Purchasing.IndexedView;
Index Compression
Data and index compression were introduced in SQL Server 2008 (available in the Enterprise and Developer
editions). Compressing an index means getting more key information onto a single page. This can lead to significant
performance improvements because fewer pages and fewer index levels are needed to store the index. There will be
overhead in the CPU as the key values in the index are compressed and decompressed, so this may not be a solution
for all indexes. Memory benefits also because the compressed pages are stored in memory in a compressed state.
By default, an index will be not be compressed. You have to explicitly call for the index to be compressed when
you create the index. There are two types of compression: row- and page-level compression. Row-level compression
identifies columns that can be compressed (for details, look in Books Online) and compresses the storage of that
column and does this for every row. Page-level compression is actually using row-level compression and then adding
additional compression on top to reduce storage size for the nonrow elements stored on a page. Nonleaf pages in an
index receive no compression under the page type. To see index compression in action, consider the following index:
CREATE NONCLUSTERED INDEX IX_Test
ON Person.Address(City ASC, PostalCode ASC);
This index was created earlier in the chapter. If you were to re-create it as defined here, this creates a row type of
compression on an index with the same two columns as the first test index IX_Test .
CREATE NONCLUSTERED INDEX IX_Comp_Test
ON Person.Address (City,PostalCode)
WITH (DATA_COMPRESSION = ROW);
Create one more index.
CREATE NONCLUSTERED INDEX IX_Comp_Page_Test
ON Person.Address (City,PostalCode)
WITH (DATA_COMPRESSION = PAGE);