The table hints are similar to query hints that have been part of SQL Server since SQL Server 2000. The hints are useful to override query optimizer behavior during SELECT, INSERT, UPDATE
, and DELETE
execution tasks. Similarly, using the index hints in a TSQL query can reduce the query optimizer from choosing a specified execute plan. There are certain restrictions on using hints, such as both NOLOCK
and HOLDLOCK
cannot be used together.
SQL Server 2008 introduced a new feature called FORCESEEK
table hint that will force seek over a scan during the query execution. Not all the time, query optimizer may choose an effective plan. Also, if the data platform is highly volatile, it requires the table statistics to be updated on a regular basis, otherwise a bad plan is generated during the frequently running query execution. The best place to provide table hints when the query intent to perform a single-time lookup with a specific value...