Hi friends, in my previous blog we learnt that sparse column cannot be part of columnstore index. In this blog we will discuss another limitation of columnstore index which is INCLUDE clause is not allowed while creating columnstore index.
Now, let’s create a test table and then we will try to create a nonclustered columnstore index using INCLUDE clause:
CREATE TABLE Test_Include
FirstName VARCHAR(10) NOT NULL,
LastName VARCHAR(10) NOT NULL
Now let’s create a nonclustered columnstore index on this table using below script:
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_TestInclude
ON Test_Include (ID, FirstName, LastName)
When we execute the above script it gives us error:
‘ CREATE INDEX statement failed because a columnstore index cannot have included column. Create the columnstore index on the desired columns without specifying any included columns.’
Also, from the error message itself we come to know that included columns are not allowed in columnstore index.
Drop the table –
DROP TABLE Test_Include
So friends, this is another one of the limitation of nonclustered columnstore index. Hope you like it :)