Pages

Mar 18, 2014

Cannot execute query. Semantic affecting hint '' appears in the '' clause of object '' but not in the corresponding '' clause. Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints match the WITH clause.

Suppose we have created a table and
inserted some data using following query:

CREATETABLE tblSource(

ID INTIDENTITYPRIMARYKEY,

vcData VARCHAR(500)

)

INSERTINTO tblSource VALUES('PC'),('Laptop'),('Mobile')

Now if we will execute this sql
query:

SELECT*FROM tblSource

OPTION (TABLE HINT(tblSource,NOLOCK));

Or

SELECT*FROM tblSource

OPTION (TABLE HINT(tblSource,FASTFIRSTROW));

We will get error message:

Cannot
execute query. Semantic affecting hint '' appears in the '' clause of object ''
but not in the corresponding '' clause.
Change the OPTION (TABLE HINTS...) clause so the semantic affecting
hints match the WITH clause.

Cause: It is due to table hint NOLOCK and FASTFIRSTROW. We cannot specify following table
hints:

Table Hints

HOLDLOCK

NOLOCK

NOWAIT

PAGLOCK

READCOMMITTED

READCOMMITTEDLOCK

READPAST

READUNCOMMITTED

REPEATABLEREAD

ROWLOCK

SERIALIZABLE

TABLOCK

TABLOCKX

UPDLOCK

XLOCK

FASTFIRSTROW

Solution:

If we want to specify above table
hints we must have to specify that table hints in WITH clause of table. For
example: