Sql – Using WITH NOLOCK Table Hint in Query Using View – Does it Propagate Within the View

lockingsqlsql-server

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.

Best Solution

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.

Related Solutions

Sql-server – SQL Server: Query fast, but slow from procedure

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.

I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
    SELECT * 
    FROM orders
    WHERE customerid = @CustID
END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT * 
    FROM orders
    WHERE customerid = @LocCustID
END

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

Sql-server – Does NOLOCK on Views propagate to the underlying tables

Yes, it does.

Using WITH NOLOCK Table Hint in Query Using View - Does it Propagate Within the View?

Best Solution

Related Solutions

Sql-server – SQL Server: Query fast, but slow from procedure

Sql-server – Does NOLOCK on Views propagate to the underlying tables

Related Question