As a SQL Server consultant, I often come across customers who are looking to upgrade their infrastructure and take advantage of the new capabilities offered by SQL Server. One interesting conversation I had was with a bank that was planning to upgrade from SQL Server 2012 to SQL Server 2016. They were particularly interested in learning about the benefits of using InMemory and ColumnStore Indexes.

During our discussion, I asked them why they hadn’t considered using a Non-Clustered ColumnStore index in SQL Server 2012. They explained that they had evaluated it, but found that the table became read-only once the index was created. This limitation made sense to them, but I was excited to inform them that in SQL Server 2016, Non-Clustered ColumnStore indexes were now updateable.

To demonstrate this capability, I decided to create a table and a Non-Clustered ColumnStore index using the syntax I remembered. However, to my surprise, I encountered an error message:

Msg 35336, Level 15, State 1, Line 1
The statement failed because specifying a key list is missing when creating an index.
Create the index with specifying key list.

Initially, I felt embarrassed and thought I had made a mistake. But upon closer inspection of the error message, I realized that it was actually guiding me towards the solution. The error message indicated that I needed to specify the key list for the index. I made the necessary changes to the code, specifying the column key values, and it worked perfectly:

CREATE NONCLUSTERED COLUMNSTORE INDEX t_non_clust_colstor_cci 
ON t_non_clust_colstor (acc_description, acc_type)
WITH (DATA_COMPRESSION = COLUMNSTORE);

As I reflected on this experience, I realized that the key values are not applicable for Clustered ColumnStore indexes, which was the reason for my initial confusion. With this realization, I felt relieved as I boarded my flight.

This incident reminded me of the power of self-explanatory error messages in SQL Server. It’s not uncommon to come across error messages that provide valuable insights for debugging. Have you ever had a similar “hero moment” where an error message guided you towards a solution? Share your experiences in the comments below!