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?

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

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.

Solarwinds

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.

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

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:

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)

Solarwinds
, , , , ,
Previous Post
How to Kill Processes Idle for X Hours? – Interview Question of the Week #152
Next Post
How to List Queries With Memory Grant and Execution Plan? – Interview Question of the Week #154

Related Posts

2 Comments. Leave new

  • The above script gives this error:

    Msg 4145, Level 15, State 1, Line 5
    An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.

    Reply
  • Msg 4145, Level 15, State 1, Line 5
    An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.

    Reply

Leave a Reply

Menu