How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153

Question: How to List All ColumnStore Indexes (CL Index) with Table Name in SQL Server?

Answer: As ColumnStore Indexes are getting more and more popular, I nowadays see lots of questions related to columnstore index. One of the most popular question, I receive during my Comprehensive Database Performance Health Check, is how to list all the columnstore index for their database.

Let us first see the script for listing all the columnstore index from your database.

List All ColumnStore Indexes

SELECT OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName,
 OBJECT_NAME(OBJECT_ID) TableName,
 i.name AS IndexName, i.type_desc IndexType
FROM sys.indexes AS i 
WHERE is_hypothetical = 0 AND i.index_id <> 0 
 AND i.type_desc IN ('CLUSTERED COLUMNSTORE','NONCLUSTERED COLUMNSTORE')
GO 

When you run above script you will see list of all the CL index with their schemaname and tablename.

If you remove the condition i.type_desc from the above script you will see all the indexes for the database.

If you want to learn more about CL index, here are few important resources. I recommend that you read them:

• SQL SERVER – Fundamentals of Column store Index
• SQL SERVER – Column Store Frequently Asked Queries
• SQL SERVER – Basics Column Store FAQ – Part II
• SQL SERVER – Column store Index Cannot be Created When Computed Columns Exist
  I was at a customer place and I was playing around with a performance troubleshooting on a SQL Server 2014 system. While we were doing this, the developer asked me if we can use a ColumnStore index for one of their history table which was large and they wanted to do some analytics on the data. This seemed like a classic candidate for this and I was confident is saying, please go ahead and it would work. At this point, the developer said that the index was not allowed on this history table.

There are two kinds of storage in the database. Row Store and Column Store. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page.

Reference : Pinal Dave (https://blog.sqlauthority.com)