Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



T-SQL Check if a Table has a ColumnStore Index
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-06-19








Solution:


sys.indexes

Type of index:

0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
4 = Spatial
5 = Clustered columnstore index. Applies to: SQL Server 2014 (12.x) through SQL Server 2017.
6 = Nonclustered columnstore index. Applies to: SQL Server 2012 (11.x) through SQL Server 2017.
7 = Nonclustered hash index. Applies to: SQL Server 2014 (12.x) through SQL Server 2017.


Select
t.name as 'Table Name',
i.[type] as 'Index Type',
i.type_desc as 'Index Type Desc',
i.name as 'Index Name'
from sys.indexes i
join sys.tables t on i.object_id = t.object_id
where i.type in (5, 6)
order by t.name asc