Force Use or Non-Use of a Columnstore Index
Sometimes, the query optimizer may make a mistake and choose to use a B-tree or heap instead of the columnstore, when in fact the query would have run faster with the columnstore. You can force the optimizer to use the columnstore with an index hint. Suppose there is a table Fact that has a columnstore index on it called ncci, and you issue the following query:
select f.a, sum(f.b)
from Fact f, Dimension d
where f.d_key = d.d_key
and d.x > 100
group by f.a
Assume that for some reason the optimizer incorrectly uses a B-tree on Fact to solve the query instead of using columnstore ncci. The following query will use the columnstore instead of the B-tree:
select f.a, sum(f.b)
from Fact f with(index=ncci), Dimension d
where f.d_key = d.d_key
and d.x > 100
group by f.a
Conversely, you can force use of a B-tree or heap instead of the columnstore with an index hint, or use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to prevent use of columnstores in the query plan. Suppose the first query above uses the columnstore but you don't want that. If Fact has a clustered B-tree index ci on it, then the following version of the query will use ci and not ncci:
select f.a, sum(f.b)
from Fact f with(index=ci), Dimension d
where f.d_key = d.d_key
and d.x > 100
group by f.a
You can also use this syntax to prevent use of the columnstore:
select f.a, sum(f.b)
from Fact f, Dimension d
where f.d_key = d.d_key
and d.x > 100
group by f.a
option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)