Table Hints starting with NOLOCK

    Table hints give us some control on the query optimizer. Microsoft do not recommend hints since SQL Server query optimizer does pretty good job. Trying to override the default behavior might cause your queries run slower. There are a lot of table hints in SQL Server and there is no way I can cover all of them in one article, so I am going to start with NOLOCK since it's the most common one, and continue to add more later.

Usage : WITH(tablehint)

Some of the table hints can be used without the WITH keyword.

Table hints can be used in DELETE, INSERT, SELECT, UPDATE and MERGE

NOLOCK

This one is pretty common, people like to use it for no reason which is scary. When you use NOLOCK, you should not expect 100% accuracy from your query. Because NOLOCK is equal to READUNCOMMITTED hint. That means followings might be occuring when you run your query

• Your query might return data that has not been committed yet. Roll backs might happen in database.
• Other transactions could move data you haven't read yet. If data is moved to an earlier location, your query would not even know about it. If data is moved to a later location, then you might have the data twice. 

Do not use it in INSERT statements, It might cause corruption. Here is an article about that.

Also NOLOCK will not be available for UPDATE or DELETE statements and it will be removed in a future version of MS SQL Server. You should remove it from your UPDATE and DELETE queries.

ROWLOCK

This tells to optimizer that it should keep locks on row scope. This will cause database to avoid escalating locks to higher scopes. It can slow your queries and block other users to run their queries.