SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Internal Query Processor Error with ColumnStore Indexes

By Mala Mahadevan,

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.

 
Total article views: 580 | Views in the last 30 days: 29
 
Related Articles
ARTICLE

How does a View work on a Table with a Clustered Columnstore Index ?

This article is about how a view works when it is created on a table with clustered columnstore inde...

ARTICLE

Stairway to Columnstore Indexes Level 12: Clustered or Nonclustered?

The previous levels of this stairway describe details, features, and limitations of columnstore inde...

BLOG

Rebuilding and Reorganizing Clustered Columnstore Indexes

Clustered Columnstore Indexes, as well as “regular” indexes, support the Rebuild and Reorganize oper...

ARTICLE

Clustered Columnstore Index Gives "Unable to find index entry" Error

The Clustered columnstore index generates "unable to find index entry" error and a memory dump after...

BLOG

How to Edit Read-Only Non-clustered Columnstore Data

As I've discussed in some of my previous posts, creating a non-clustered Columnstore index will make...

Tags
 
Contribute