We have seen that once a table has been physically implemented as a clustered columnstore index, further indexes are not supported. We shall now take a look at how to circumvent this limitation, albeit at the cost, perhaps substantial, of additional hard drive space.
While we cannot created indexes on a clustered columnstore index table, we can create an indexed view. Once this indexed view, often referred to as a materialized view, exists, we can create nonclustered indexes on the indexed view. These nonclustered indexes can be used for precisely those tasks that cannot be achieved directly. Nonclustered indexes on an indexed view can be used to ensure uniqueness of key columns, and they can improve the performance of point queries and any other queries that might benefit from a class index seek rather than columnstore access.
In this example, we will create an indexed view that covers all the columns in the table. Of course, this is costly in terms of disk space. If the disk space cost is too high, an indexed view can be created on a subset of columns, but Murphy’s Law guarantees that the omitted columns will soon be required by an important query.
CREATE VIEW IV_FactSales WITH SCHEMABINDING
Once the view has been defined, the view is materialized by creating a unique clustered index on the view. This, of course, is a classic B-tree clustered index.
CREATE UNIQUE CLUSTERED INDEX CI_IV_FactSales ON IV_FactSales(SalesKey)
Once we have an indexed view, we can create as many nonclustered indexes as we need. In this example, we will create an index on DateKey that covers the SalesAmount column.
CREATE INDEX IX_DateKeySalesAmount ON IV_FactSales(DateKey) INCLUDE(SalesAmount)
Let’s check to see if the new indexes help with a point query:
SELECT * FROM FactSales WHERE SalesKey = 42
The SQL Server Query Optimizer does not recognize the indexed view as an available resource for this query . However, if we explicitly query the indexed view by name, the goal is achieved.
SELECT * FROM IV_FactSales WHERE SalesKey = 42
The previous query had a calculated cost of 1.74666 and incurred 8683 logical reads. This query uses the indexed view with a cost of 0.0032831 and 3 logical reads.
Now we’ll test the effectiveness of our new nonclustered covering index using a query from part 2:
SELECT SUM(SalesAMount) FROM FactSales WHERE DateKey >= ’12/1/2007′ AND DateKey<’12/2/2007′
We see that the Query Optimizer has using the covering index on the indexed view and that this has substantially lowered the query cost. The original cost of this query measured in part 2 using the clustered columnstore index was 0.726925, with 17312 logical reads. With the covering index, the cost was reduced to 0.0364335 with 17 logical reads.
We have seen that although we cannot directly create additional indexes on a table implemented as a clustered columnstore index, we can do so indirectly be creating an indexed view. At the cost of additional storage space on the hard drive, this method permits us to achieve the goals traditionally met by classic indexes on a classic B-tree clustered index, including providing support for uniqueness of keys and providing efficient ”needle-in-a-haystack” seeks of row data.