SQL Server Hints

SQL Server Hints

• QUERY hints - modify the behavior of the entire statement: http://msdn.microsoft.com/en-us/library/ms181714.aspx
• JOIN hints - modify the join method: http://msdn.microsoft.com/en-us/library/ms173815.aspx
• TABLE hints - modify the type of access for a given table: http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx

CATEGORY	HINT	Available for	Info
JOIN	LOOP	SELECT/UPDATE/DELETE	Not applicable for RIGHT OUTER or FULL joins.
JOIN	HASH	SELECT/UPDATE/DELETE
JOIN	MERGE	SELECT/UPDATE/DELETE
JOIN	REMOTE	SELECT/UPDATE/DELETE	Only for INNER JOINs. Not applicable with COLLATE
QUERY	RECOMPILE	SELECT/UPDATE/DELETE
QUERY	FORCE ORDER	SELECT/UPDATE/DELETE
QUERY	ROBUST PLAN	SELECT/UPDATE/DELETE
QUERY	KEEP PLAN	SELECT/UPDATE/DELETE
QUERY	KEEPFIXED PLAN	SELECT/UPDATE/DELETE
QUERY	EXPAND VIEWS	DML statements	Only for statement containing views.
QUERY	HASH GROUP	SELECT	Only when GROUP BY, COMPUTE and DISTINCT clauses are used.
QUERY	ORDER GROUP	SELECT/UPDATE/DELETE	Only when GROUP BY, COMPUTE and DISTINCT clauses are used.
QUERY	MERGE UNION	SELECT	Only for statements chained using UNION
QUERY	HASH UNION	SELECT	Only for statements chained using UNION
QUERY	CONCAT UNION	SELECT	Only for statements chained using UNION
QUERY	LOOP JOIN	SELECT/UPDATE/DELETE
QUERY	MERGE JOIN	SELECT/UPDATE/DELETE
QUERY	HASH JOIN	SELECT/UPDATE/DELETE
TABLE	INDEX()	DML statements	Only for tables and views with indexes. No support for indexed views.
TABLE	KEEPIDENTITY	INSERT	Only for INSERT statements using OPENROWSET clause with BULK option.
TABLE	KEEPDEFAULTS	INSERT	Only for INSERT statements using OPENROWSET clause with BULK option.
TABLE	HOLDLOCK	DML statements	Not applicable for SELECT statements using FOR BROWSE clause.
TABLE	IGNORE_CONSTRAINTS	INSERT	Only for INSERT statements using OPENROWSET clause with BULK option.
TABLE	IGNORE_TRIGGERS	INSERT	Only for INSERT statements using OPENROWSET clause with BULK option.
TABLE	NOLOCK	SELECT/UPDATE/DELETE	Not applicable for the target table in UPDATE/DELETE statements.
TABLE	NOWAIT	DML statements
TABLE	PAGLOCK	DML statements
TABLE	READCOMMITED	DML statements
TABLE	READCOMMITEDLOCK	SELECT/UPDATE/DELETE
TABLE	READPAST	SELECT/UPDATE/DELETE	Not applicable for the target table in UPDATE/DELETE statements.
TABLE	READUNCOMMITED	SELECT/UPDATE/DELETE	Not applicable for the target table in UPDATE/DELETE statements.
TABLE	REPEATABLEREAD	DML statements
TABLE	ROWLOCK	DML statements
TABLE	SERIALIZABLE	DML statements	Not applicable for SELECT statements using FOR BROWSE clause.
TABLE	TABLOCK	DML statements
TABLE	TABLOCKX	DML statements
TABLE	UPDLOCK	DML statements
TABLE	XLOCK	DML statements
TABLE	FASTFIRSTROW	DML statements