SQL Server Portal

ColumnStore Index & Forceseek table hints enhancements are two new features introduced in SQL Server 2012. The error message we will discuss today is related to these two enhancements because they both cannot be used together.

Let’s discuss this in detail:

Message Number: 366

Severity : 16

Error Message: The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘%.*ls’ cannot be used with the column store index ‘%.*ls’.

Error Generation:
Let me create a sample to demonstrate this error.

--First Create a columnstore index on [Purchasing].[PurchaseOrderDetail]
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_PurchaseOrderDetail_ColumnStore]
ON [Purchasing].[PurchaseOrderDetail]
(
[ReceivedQty],
[ProductID]
)
WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

--Try to use it with FORCESEEK table hint

Select * from [Purchasing].[PurchaseOrderDetail]
WITH (FORCESEEK,INDEX (IX_PurchaseOrderDetail_ColumnStore))
--OUTPUT

Msg 366, Level 16, State 1, Line 1
The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘PurchaseOrderDetail’ cannot be used with the column store index ‘IX_PurchaseOrderDetail_ColumnStore’.

Ooopps…… I am unable to execute it.

Resolution:

The reason behind this error is that we cannot use COLUMNSTORE INDEX with FORCESEEK table hint because columnstore index already boosted the performance of the query on that particular table. So, we do not use any further FORCESEEK table hints on that table. But sometime we create columnstore index on some other fields and we need to do forceseek on some other fields, so in that case we can create another non cluster index and point it to forceseek table hint.

Give below are the script.

--Create a new noncluster index BUT NOT COLUMNSTORE INDEX
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_Sample] ON [Purchasing].[PurchaseOrderDetail]
(
[ReceivedQty],
[ProductID]
)
WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

--Execute the query against the index
Select * from [Purchasing].[PurchaseOrderDetail]
WITH (FORCESEEK,INDEX (IX_PurchaseOrderDetail_Sample))
Where ProductID>2 And ReceivedQty>5
--OUTPUT

Conclusion :
Remember, whenever you FORCESEEK with index, make sure that the index is not a columnstore index. However, if you need to use FORCESEEK table hint with index on the same table, you can create another index without columnstore and use it in FORCESEEK.

Clean Database :
Execute the given below script to drop the sample indexes.

DROP INDEX [IX_PurchaseOrderDetail_ColumnStore]
ON [Purchasing].[PurchaseOrderDetail]
DROP INDEX [IX_PurchaseOrderDetail_Sample]
ON [Purchasing].[PurchaseOrderDetail]