Menu
If a "WITH NOLOCK" query hint is used on a View in SQL Server, does it propagate that hint to the view definition itself, even if NOLOCK is NOT used for the raw tables in the View definition? The reason to need this is that sometimes the support staff wants to do huge time-consuming queries but would rather not force this lock on all queries using the view within the application itself.
295
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads. Read further to better understand the use of NOLOCK.
The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.
When you query a view, it looks exactly like any other database table. You can display it in much the same way as you can any other table, with few restrictions. Changing data through a view has some limitations (explained later in this chapter). For now, consider the simplest case: a view based on a single table.
NOLOCK for CTEs work the same way as with everything else: it causes inconsistent results. Use SNAPSHOT instead, see SQL Server 2005 Row Versioning-Based Transaction Isolation.
Yes, NOLOCK will propagate to the tables used by the view definition (at least in SQL Server 2005).
See Table Hints in MSDN:
In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.
However,
If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.
If you're using indexed views you might want to read a bit more as there are some special cases there too.
Also see View Resolution for more info.
61
Just to supplement Rory's excellent answer.
He writes "Yes, NOLOCK will propagate to the tables used by the view definition (at least in SQL Server 2005)."
In fact this will work in SQL 2000 as well. From BOL:
Because select_statement uses the SELECT statement, it is valid to use and hints as specified in the FROM clause. For more information, see FROM and SELECT.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
