FORCESEEK and FORCESCAN are the new enhancements in Denali CTP3. Before Denali, FORCESEEK hint exists and enhanced in Denali CTP3. FORCESCAN table hint has been added.

FORCESEEK: Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. We can specify it without an index or with an index or Index with column names.

FORCESCAN: Specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation.

Here are few examples of queries with hints of FORCESEEK and FORCESCAN.

-- with FORCESEEK
SELECT  ObjectName FROM ObjectItems WITH (FORCESEEK) 
WHERE ObjectType = 'SQL_STORED_PROCEDURE' 

-- with FORCESEEK and specified index
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate))
WHERE ObjectType = 'SQL_STORED_PROCEDURE' 

-- with FORCESEEK and specified index using at least the specified index columns
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK(IX_ObjectType_CreateDate(ObjectType,CreateDate)))
WHERE ObjectType = 'SQL_STORED_PROCEDURE'

-- with FORCESCAN
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN)
WHERE ObjectType = 'SQL_STORED_PROCEDURE' 

-- with FORCESCAN and specified index 
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN, INDEX(IX_ObjectId))
WHERE ObjectType = 'SQL_STORED_PROCEDURE' 

Published under: SQL Server Tips ·  ·  ·  ·