Today is Friday and tomorrow is the weekend. I just thought, let us explore something new but additionally give you all a quick puzzle to explore about index hints.
SQL Server Denali has new Query Hint – FORCESCAN. In earlier version of SQL Server we already have Query Hint FORCESEEK but now the counter part also exists. The quick understanding is there will be cases when FORCESEEK or FORCESCAN will be helpful and improve the performance of the query.
Now here is the quick contest-
1) Write a Query where using FORCESCAN hint which will improve the performance of the query.
2) Write a Query where using FORCESEEK hint which will improve the performance of the query.
The winner will get my printed book SQL Server Wait Stats [Amazon] printed book FREE (shipped anywhere in the world).
The FORCESEEK table hint forces the query optimizer to use only an index seek operation. Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement.
Update: The puzzle is over at this point of time, but you are welcome to submit your answers.
Please leave your notes in the comments section of this blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)