Database Reference
In-Depth Information
As you can see, columnstore indexes significantly reduce the I/O load in the system as well as the CPU and
Elapsed time of the queries. The difference is especially noticeable in the case of batch-mode execution where the
query ran almost ten times faster as compared to a row-mode clustered index scan.
Columnstore Index Internals
There are two types of columnstore indexes available in SQL Server. SQL Server 2012 supports only nonclustered
columnstore indexes. SQL Server 2014 supports clustered columnstore indexes in addition to nonclustered ones.
Nevertheless, both clustered and nonclustered columnstore indexes share the same basic structure to store data.
■
Note
We will discuss clustered columnstore indexes in greater detail in Chapter 35, “Clustered Columnstore Indexes.”
Nonclustered Columnstore Indexes
You can create a nonclustered columnstore index with the
CREATE COLUMNSTORE INDEX
command. You have already
seen this command in action in Listing 34-3 in the previous section.
There are several things worth noting about this command. The
CREATE COLUMNSTORE INDEX
command looks like
the regular
CREATE INDEX
command, and it allows you to specify the destination filegroup or partition schema for the
index. However, it supports only two options during index creation such as,
MAX_DOP
and
DROP_EXISTING
.
A nonclustered columnstore index can include up to 1,024 non-sparse columns. Due to the nature of the index,
it does not matter in what order the columns are specified; that is, data is stored on a per-column basis.
Similar to B-Tree nonclustered indexes, nonclustered columnstore indexes include a
row-id,
which is either a
clustered index key value or the physical location of a row in a heap table. This behavior allows SQL Server to use the
Columnstore Index Scan
operation to perform
Key Lookup
afterwards. It is worth noting that columnstore indexes do
not support a Seek operation because the data in those indexes is not sorted. We will talk about data storage in more
detail shortly.
Listing 34-8 shows an example of a query that uses a
Columnstore Index Scan
with
Key Lookup
operators.
Listing 34-8.
Query that triggers Key Lookup operation
select OrderId, Amount, TaxAmt
from dbo.FactSales
where ArticleId = -1
Figure
34-14
shows the execution plan for the query shown in Listing 34-8. It is worth noting that the
OrderId
column is included in the output list of the
Columnstore Index Scan
operator. That column has not been explicitly
defined in the columnstore index; however, it is part of the clustered index key in the table.
