Table Hints - Points to remember

·         The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead.

·         Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

·         If a table contains computed columns that are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables and are not propagated.

·         SQL Server does not allow for more than one table hint from each of the following groups for each table in the FROM clause:

• Granularity hints: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, or TABLOCKX.
• Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.


Nolock
·         A compatibility level of 80 does not allow nolock without a WITH.
·         Error 601 - When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft® SQL Server™ is not able to continue the scan.
·         Table hints are not sent through the client stack you can use a query with OPENQUERY to address the relevant SQL Server and submit any table hints you want the external server to process. The query will be just passed through and interpreted by the linked server.
·         Create a view on the remote server that has the nolock specified in it, or a stored procedure and call through one of those mechanisms to get the data without causing locking.
·         NOLOCK generally helpful in blocking scenario not in Dead lock.
·         Enabling any of the snapshot options has some repercussions. It causes SQL Server to add 14 bytes to every updated row. To create the version store from which SQL Server constructs the snapshot, SQL Server uses tempdb
·         NOLOCK will read the data irrespective of the (transaction) lock on it, and NOWAIT will return error if the original table has (transaction) locked on it.