SQL SERVER — Columnstore index

Today, I want to show you how to create columnstore index and its performance.

Our example is Posts table from StackOverfolw Database. It contains 46.947.633 rows and takes 121 GB of space.

I created clustered columnstore index.

Now our table Posts_Columnstore takes 32.5GB of space.

Lets make a first query.

For rowstore table, engine produces a result in 56 seconds, for columnstre table 10 seconds.

We have only one clustered index on Id column in Posts table. Lets make covering indexex for this query.

It is better 20 seconds. Query uses both indexes.

Lets try another query.

Columnstore: 13 sec.

Rowstore: 1 min. 31 sec.

As we know, columnstore indexex are good choice when we work with numerical, aggregated data. So, we try to select a few records.

The crucial thing in this query is LIKE ‘%%’ clause. We try to pick data from column with low selectivity and we need to examine strings that contains “Columnstore index” substring.

Columnstore: 3 min. 50 sec.

Rowstore: 8 sec.

BI Developer (Microsoft Stack)