How to override default behavior of SQL Server Optimizer.

Problem: How to override default behavior of SQL Server Optimizer.

Solution: Table Hint , Starting SQL Server 2008 or higher.

There are few key words which make the SQL Server Optimizer to choose a specific execution plan instead of default plans, the following are a few table hints we can use to specify in the queries to choose based on the need to override the execution plan in the optimizer for better performance.

  KEEPIDENTITY  

  KEEPDEFAULTS  

  HOLDLOCK  

  IGNORE_CONSTRAINTS  

  IGNORE_TRIGGERS  

  NOLOCK  

  NOWAIT 

  PAGLOCK  

  READCOMMITTED  

  READCOMMITTEDLOCK  

  READPAST  

  REPEATABLEREAD  

  ROWLOCK  

  SERIALIZABLE  

  SNAPSHOT  

  TABLOCK  

  TABLOCKX  

  UPDLOCK  

  XLOCK

Example:

HOLDLOCK: -- This is an exclusive session lock and the query results are consistent and restricts the modifications on the row.

SELECT * FROM dbo.Invoice WITH (HOLDLOCK)

WHERE invoiceid = 235786

UPDLOCK – This allows the rows to lock as prep before the updates

Update dbo.Invoice WITH (UPDLOCK)

SET invoiceid = 235784

WHERE invoiceid = 235786

NOLOCK – This allows the readers to retrieve without holding any locks on the table

SELECT * FROM dbo.Invoice WITH (NOLOCK)