Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

FORCESEEK and FORCESCAN - New enhancements with Table hints in SQL Server Denali CTP3

Jul 28 2011 4:55AM by Paresh Prajapati   

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' 
Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Paresh Prajapati
6 · 23% · 7465
5
 
0
Knew
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

1  Comments  

  • These will be useful for developers a lot while tuning procedures.......

    commented on Jul 28 2011 5:06AM
    Ramireddy
    2 · 40% · 12972

Your Comment


Sign Up or Login to post a comment.

"FORCESEEK and FORCESCAN - New enhancements with Table hints in SQL Server Denali CTP3" rated 5 out of 5 by 5 readers
FORCESEEK and FORCESCAN - New enhancements with Table hints in SQL Server Denali CTP3 , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]