Types of Table Hints in SQL Server

In my previous post, we have gone through about the basic of SQL Server Hints. Now here we are moving with type of Table Hints.

There are various types of Table Hints. Some of the important and useful for day to day basis are as below:-

NOEXPAND:-

Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

Normal views have to be expanded because they don't actually stored any data, they're just saved select statements.

INDEX (index_value [,... n ] ) | INDEX = ( index_value):-

Index hints (a form of a table hint) are used to specify which index or indexes you want used when a query runs. When you specify one or more indexes, the Query Optimizer will use them when creating the execution plan for your query. Sometimes the SQL Server query optimizer does not pick the best index to use in a join operation.  This can be caused by a multitude of factors (out of date statistics, wrong indexing strategy for a table, etc) ... for what ever reason it happens, there is a way to force SQL Server to pick the correct index at runtime.  This is called an INDEX HINT.

NOLOCK:-

This hint allows queries to run even while updates are being performed on the tables you want to view.  It allows you to do what is called a “Dirty Read” on the data.  This term is used because you could potentially view data that has not been committed yet and could get rolled back.  We use this hint to avoid blocking on our site.

TABLOCK:-

Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.

TABLOCKX:-

The TABLOCKX hint specifies that an exclusive lock will be taken on the table until the end of the Transact-SQL statement or the end of the transaction. The TABLOCKX provides the worst concurrency.