Introduction to Columnstore Indexes - Pro SQL Server Internals - page 711

Figure 34-18. dbo.FactSales table allocation units
Metadata
SQL Server provides two columnstore index-related data management views. Let's look at them in depth.
sys.column_store_segments
The sys.column_store_segments view returns one row for each column per segment.
Listing 34-12 shows a query that returns information about the IDX_FactSales_ColumnStore columnstore index
defined on dbo.FactSales table. There are a couple of things that you should note here. First, the sys.column_store_
segments view does not return the object_id or index_id of the index. This is not a problem because a table can have
only one columnstore index defined. However, you need to use the sys.partitions view to obtain the object_id
when it is required.
Listing 34-12. Examining the sys.column_store_segments view
select p.partition_number as [partition], c.name as [column]
,s.column_id, s.segment_id, p.data_compression_desc as [compression]
,s.version, s.encoding_type, s.row_count, s.has_nulls, s.magnitude
,s.primary_dictionary_id, s.secondary_dictionary_id, s.min_data_id
,s.max_data_id, s.null_value
,convert(decimal(12,3),s.on_disk_size / 1024. / 1024.) as [Size MB]
from
sys.column_store_segments s join sys.partitions p on
p.partition_id = s.partition_id
join sys.indexes i on
p.object_id = i.object_id
left join sys.index_columns ic on
i.index_id = ic.index_id and
i.object_id = ic.object_id and
s.column_id = ic.index_column_id
left join sys.columns c on
ic.column_id = c.column_id and
ic.object_id = c.object_id
where
i.name = 'IDX_FactSales_ColumnStore'
order by
p.partition_number, s.segment_id, s.column_id
Second, like regular B-Tree indexes, nonclustered columnstore indexes include a row-id , which is either the
address of a row in a heap table or a clustered index key value. In the latter case, all columns from the clustered index
are included in the columnstore index, even when you do not explicitly define them in the CREATE COLUMNSTORE
INDEX statement. However, these columns would not exist in the sys.index_columns view, and you need to use an
outer join when you want to obtain the column name.