A question that I often get is what a table hint is and how it is used?
Well, basically when you write some code in TSQL, then the SQL Server needs to translate it to something it can execute.
That is a multistep process, and the last steep: is to optimize the execution. Basically the SQL server says: “I can now run the statement, but how can I run it in the best possible way?”. This is a costbased process, where the Query optimizer (that is the name of the last step) calculates a cost for different execution plans, and then it chooses the one with the lowest cost.
So if you e.g. have a statement like:
Then the SQL Server finds what it think is an optimal plan of execution. It is not necessarily the best plan, but it is a good plan.
For the above query, the executionplan looks like:
The executionplan is read from right to left, and for this moment it does not matter what it means. the important think is that with tablehints you can override the way the query is executed.
Try the following:
from Person.address with (index(1))
The executionplan looks like this:
So by adding the table hint WITH (index(1)), then you can change the way the sql server executes your statement.
What else does table hints do?
Well, it does a lot of things, and you can read about it here, but one thing that I would like to show is using tablehints in a practical world. If you have a system with a lot of users and you want to extract some data from a table that are heavily used, then you could use the table hint with (NOLOCK).
It means that the query does not take any locks and does not care about any locks taken on an object. In other terms: This query will execute no matter if other users are updating the table or not AND it will not wait on them to complete.
The risk is that you get inconsistent data, but the benefit is that you get data!! This is very useful for reporting, where e.g. you need data that are out of the range of data that are updated.