Description of the WITH (NOLOCK) option when SQL Select

I used to see my senior developers use with (NOLOCK) when querying in SQL Server and wonder why they use. Now I explored it's and found that it's useful to improve the performance in executing the query . However there is a disadvantage in using it. The disadvantage is, the one of May and not being sure that they was getting the data which is currently being updated in the Table, ie, without lock protection, you cannot being guaranteed that the data isn ' t changing during the time that's the query is Running. I referred this link and found it pretty useful.

To improve the query performance of SQL, we generally consider the index as the first consideration. In fact, in addition to the establishment of the index, when we are under the SQL command, in the syntax of adding a section with (NOLOCK) can improve the environment of large online query data set by lock, thereby improving the performance of the query.

However, it is important to note that the SQL select with (NOLOCK) may cause dirty Read.

For example:

SELECT  COUNT (UserID)   
From    EMPLOYEE with (NOLOCK)   
        JOIN Working_group with (NOLOCK)   
        On EMPLOYEE. UserID = Working_group. Userid  

Because SQL Server performs a corresponding lock consistency check. To improve the performance of the overall database query, add with (NOLOCK) after the table name in your select syntax, although (NOLOCK) is also possible, but Microsoft recommends that you add with.

In addition to simple select, a select syntax with join is also available. But delete, INSERT, update these need to transaction instruction is not ...

Some files say that the efficiency of SQL queries added with (NOLOCK) can be increased by 33%.
Add with (NOLOCK) to tell SQL Server, our select instruction does not need to consider the current table transaction lock state, so the performance will be significantly improved, and the database system lock phenomenon will be significantly reduced ( Contains dead Lock).

It is important to note that because with (NOLOCK) does not take into account the current table's transaction lock, there are certain materials that are in multiple phase transactions (for example, transaction transactions across multiple table--such as the withdrawal system). With (NOLOCK) will let the current processing of the transaction process data is ignored ...

Speak a little vernacular, that is, when using NOLOCK, it allows you to read data that has been modified but not yet completed. So if there is a need to consider the real-time integrity of transaction transactional data, using with (NOLOCK) takes a good look.

If you do not need to consider transaction,with (NOLOCK) may be a useful reference.

Note 1:with (< table_hint >)
Specifies the table scan, one or more indexes that are used by the query optimizer,
This data table is used by the query optimizer, as well as using lockdown mode for this statement.

Note 2:with (NOLOCK) corresponds to the last few minor differences between the NOLOCK and with (NOLOCK) READ uncommitted: Synonyms in 1:sql05, only supported with (NOLOCK); The 2:with (NOLOCK) notation is very easy to specify the index again. Cross-server query statements can not be used with the WITH (NOLOCK) only nolock the same server query when with (NOLOCK) and nolock are available such as SQL code
SELECT * FROM [Ip].a.dbo.table1 with (NOLOCK) This will prompt with the error select * from A.dbo.table1 with (NOLOCK) so that you can