info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Check if a Table has a ColumnStore Index
by BF (Principal Consultant; Architecture; Engineering)
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