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:

CREATE TABLE tblSource(
    ID INT IDENTITY PRIMARY KEY,
    vcData VARCHAR(500)
)

INSERT INTO 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:

SELECT * FROM tblSource WITH(NOLOCK)
OPTION (TABLE HINT (tblSource, NOLOCK));

Or

SELECT * FROM tblSource WITH(FASTFIRSTROW)
OPTION (TABLE HINT (tblSource, FASTFIRSTROW));

Output:

ID
vcData
1
PC
2
Laptop
3
Mobile

No comments:

Post a Comment