Posted in Development

SQL Server – Columnstore Index creation error: A columnstore index cannot be created on a SPARSE column

Hi friends, as we know that in SQL Server 2012 a new feature Columnstore index was introduced which made a great change in area of performance optimization like reducing I/O. From SQL server 2012 to 2016 there were many changes made in columnstore index. In SQL Server 2012 only nonclustered columnstore index were introduced.

In the next few blogs we will discuss some limitations of columnstore index.

What is columnstore index?

From BOL: columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore.

Now, let’s create a test table with a SPARSE column property in it and then we will try to create a nonclustered columnstore index including that column:



                ID INT,

                FirstName VARCHAR(10) NOT NULL,

                MiddleName VARCHAR(10) SPARSE,

                LastName VARCHAR(10) NOT NULL


Sparse column is used to handle NULL or ZERO values as it doesn’t take any space.

Now let’s create a nonclustered columnstore index on this table using below script:


ON Test_Sparse (ID, FirstName, MiddleName, LastName)

When we execute the above script it gives us error:

CREATE INDEX statement failed because a columnstore index cannot be created on a sparse column. Consider creating a nonclustered columnstore index on a subset of column that does not include any sparse column.’


From the error message itself we come to know that sparse column cannot be part of columnstore index.

Drop the table –

DROP TABLE Test_Sparse

So friends, this is one of the limitation of nonclustered columnstore. We will learn some more limitations of columnstore index in my next blog.

Hope you like it J



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s