SQL Server Portal

In my earlier articles, I wrote about new enhancement of FORCESEEK table hint. It is a very handy feature when you have more than one index on the table and you want to select not only the index but also the column name of the index in FORCESEEK table hint. But you should be very careful about the syntax and parameters of this table hint. Recently, I came across a new error message related to FORCESEEK table hint.

Let me explain this error in detail :

Message Number: 365

Severity : 16

Error Message: The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘%.*ls’ specified more seek columns than the number of key columns in index ‘%.*ls’.

Error Generation:

Let me create a NONCLUSTERED INDEX to demonstrate this error.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_Sample]
ON Purchasing.[PurchaseOrderDetail]
([PurchaseOrderID]
,[ProductID] ASC)
WITH (DROP_EXISTING = OFF) ON [PRIMARY]

Once you create the index, lets write a query using the above index and forceseek table hint.

USE [AdventureWorks2012]
GO
SELECT * FROM Purchasing.PurchaseOrderHeader AS H
INNER JOIN Purchasing.PurchaseOrderDetail AS D
WITH (FORCESEEK
(IX_PurchaseOrderDetail_Sample
(PurchaseOrderID
,ProductID
,OrderQty
)))
ON H.PurchaseOrderID =D.PurchaseOrderID
WHERE
D.ProductID>100
AND D.PurchaseOrderID>10
AND D.OrderQty>0

Msg 365, Level 16, State 1, Line 1
The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘PurchaseOrderDetail’ specified more seek columns than the number of key columns in index ‘IX_PurchaseOrderDetail_Sample’.

Ooopps…… I am unable to execute it.

Resolution:

If you look at the above query, you will realize that there are two key columns ([PurchaseOrderID], [ProductID]) available in IX_PurchaseOrderDetail_Sample index. But what you are trying to do in the above query is that you included an additional column [OrderQty] that is not part of the index.

Lets remove the Order Qty (line # 7) column from the FORCESEEK table hint and execute the query again.

USE [AdventureWorks2012]
GO
SELECT * FROM Purchasing.PurchaseOrderHeader AS H
INNER JOIN Purchasing.PurchaseOrderDetail AS D
WITH (FORCESEEK
(IX_PurchaseOrderDetail_Sample
(PurchaseOrderID
,ProductID

)))
ON H.PurchaseOrderID =D.PurchaseOrderID
WHERE
D.ProductID>100
AND D.PurchaseOrderID>10
AND D.OrderQty>0
--OUTPUT

Conclusion :

Remember, whenever you use FORCESEEK hint on a table and define the index name and column name as well, the column name must be part of the Index key columns.

Read Full Post »