SQL SERVER – Columnstore Index Cannot be Created When Computed Columns Exist

Explorations working with SQL Server never stop and I get an opportunity to learn something every other day. Today I thought of bringing pout some learning that happened to me by accident. As I was not sure and assumed certain things, these came as a surprise learning for me. So let us see what is the issue at hand, which is about Columnstore Index and Computed Column.

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.

This got me curious and then I said, can we setup the same together. For which the developer was completely excited because he was getting a free support beyond what was agreed upon. To simulate the same, see the script below:

CREATE TABLE test_tbl (id_col1 INT, compute_col2 AS id_col1 + 50 PERSISTED)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCS_ind1 
ON test_tbl (compute_col2)
GO

This shows the error is clear text. It reads like below:

Msg 35307, Level 16, State 1, Line 1
The statement failed because column ‘compute_col2’ on table ‘test_tbl’ is a computed column.
Columnstore index cannot include a computed column implicitly or explicitly.

As it shows, this is something that even I was not aware and a moment of realization took the front seat. As you can see a non-clustered Index cannot be created when a computed column is present on the table.

I went a step ahead and even tried to create a clustered columnstore index to see if anything changes. Nothing different even now, the error message was no different even now. The same as before as shown in the figure below.

I think this was a great learning for me because I fell for the assumptions created in my mind that the index was on a normal table. I understand now to gather all the requirements before jumping to conclusions.

Having said that, did you ever have a reason to create such indexes on your environments? What were those? Please share the same via comments.

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