As I was doing some testing for upgrading our data warehouse to SQL Server 2016, I found something interesting to share. I have a few tables that have columnstore indexes in them, in addition to a clustered btree index. On SQL Server 2016, I tried to drop and recreate the columnstore index and would get an error that the optimizer could not create the execution plan. I thought it was initially due to memory constraints, so I tried increasing the memory on the server, but still continued to have the same problem.

I finally narrowed the issue down to this: the key on which I was creating the btree clustered index had a computed column in it. When I got rid of this, I was able to create both the columnstore index as well as the clustered btree index without any issues. I can, of course, choose my clustered index to be the columnstore index on 2016 also. If I want the computed column to stay as part of the key, the solution would be to create the clustered index as columnstore, and then go with a non clustered btree index if necessary.

The steps to recreate the error as below.

CREATE TABLE [dbo].[BillDate](
       [DateID]  AS (isnull((datepart(year,[date])*(10000)+datepart(month,[date])*(100))+datepart(day,[date]),(-1))) PERSISTED NOT NULL,
       [Date] [date] NULL,
       [DayOfWeekNumber] [int] NULL,
       [DayOfWeekName] [varchar](20) NULL
CONSTRAINT [PK_Date] PRIMARY KEY NONCLUSTERED
(
       [DateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCS_Billdate] ON [dbo].[Billdate]
(
       [Date],
       [DayOfWeekNumber],
       [DayOfWeekName]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
CREATE CLUSTERED INDEX [pk_Billdate] ON BillDate(DateID)

Steps 2 or 3, may be reversed and either way – the last step gives an error as below:

This problem can be solved by

1. Not using a computed field in primary key of btree clustered index or columnstore index (Columnstore indexes cannot include computed columns anyway).

OR

2. Creating the btree clustered index using computed column and any other columns, avoiding columnstore indexes entirely.

OR

3. Creating nonclustered columnstore index without including computed column and a nonclustered btree index on computed column.