Columnstore Indexes- Identification and space usage

How to find tables with columnstore indexes

As we know that SQL 2012 has added feature of Nonclustered Columnstore Index. SQL 2014 onwards it will support Clustered Columnstore Indexes also.

As databases are growing we also need to track which tables has clustered and nonclustered columnstore indexes.



Sys.indexes has added two more type values to find tables which has columnstore indexes

Type 5 for CLUSTERED COLUMNSTORE

If the table has clustered columnstore index it will not support any other indexes.

Type 6 for NONCLUSTERED COLUMNSTORE

If it has nonclustered columnstore index it can support additional indexes.

CREATE TABLE temp1

(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX  idx1 ON temp1

Creating other index will fail as columnstore index doesn't support any other index. 

CREATE INDEX IDX2 ON temp1 (id)

 

CREATE TABLE temp2

(id INT)

CREATE NONCLUSTERED COLUMNSTORE INDEX  idx1 ON temp2(id)

CREATE INDEX idx  ON temp2(id)

SELECT OBJECT_NAME(OBJECT_ID),* FROM SYS.INDEXES WHERE TYPE IN (5,6)

How to get Index Size 

Also we can run below query to get space used by indexes.

DECLARE @i INT

SET @I = 1

WHILE @i <= 1000000

BEGIN

       INSERT INTO temp1 VALUES (@i)

       SET @i = @i+1

END

SELECT

    t.NAME AS TableName,

    p.rows AS RowCounts,

       SUM(a.total_pages) * 8 AS TotalSpaceKB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE

    t.NAME = 'temp1'

GROUP BY

    t.Name, p.Rows

ORDER BY

    t.Name