AFAIK columnstore indexes store min/max values for segments (1 million rows), so this information can be used to prune not needed data. This feature seems to work with equality predicates, but not with more complex cases like joins or subquery filters. In SQL Server 2014, is it possible to use columnstore metadata filtering with joins or subqueries?
Below is my test case:
--create table generate_series with 67_108_864 rows (runs about 5 mins) set nocount on; create table generate_series(c bigint); insert into generate_series select 1; declare @i int = 26; while @i != 0 begin insert into generate_series WITH (TABLOCK) select (select count(*) from generate_series) + row_number() over (order by (select null)) as c from generate_series; set @i = @i - 1; end; create nonclustered columnstore index cstore on generate_series(c); --Test queries -- 0 seconds (very fast) select * from generate_series where c = 100; -- 8 seconds (slow) select 100 as c into #tmp; select * from generate_series where c in (select c from #tmp);