Using with Nolock Table Hint in Query Using View - Does It Propagate Within the View

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

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.

Do I need NO LOCK in my CREATE View query

I will answer your question first.

It is better to have the NOLOCK hint on the view from outside instead of on the tables in the view.

For example

select * from vwTest with (nolock)

or

set transaction isolation level read uncommitted
select * from vwTest

Doing it this way you as the creator is catering for a wider user base who may or may not be as experienced at SQL as yourself. By not encapsulating NOLOCK hints in the view encourages other developers to really think about how they would like to retrieve the data in a safe and efficient manner.

Now more info on NOLOCK. It is a nice trick if you are 100% sure the underlying data is no longer changing, a good example is when a ETL system finishes loading data for the day. It is also handy in a read-only reporting system where again you are sure there is no data movement between report runs.

Otherwise, it is not a recommended hint to use in your system. It does more harm than good if you don't really understand the implications.

Please refer to the following links for the damages NOLOCK can cause:
Previously committed rows might be missed if NOLOCK hint is used

SQL Server: VIEW with NO LOCK but called without NO LOCK

This is very easy to test. Firstly, in a sandbox environment, run the following:

CREATE TABLE dbo.MyTable (ID int);
GO

CREATE VIEW dbo.MyView AS

    SELECT ID
    FROM dbo.MyTable WITH (NOLOCK);
GO

CREATE VIEW dbo.MyView2 AS

    SELECT ID
    FROM dbo.MyTable;
GO

BEGIN TRANSACTION Test;

    INSERT INTO dbo.MyTable
    VALUES(1);

Notice I don't COMMIT the transaction. Now in a new window, run SELECT * FROM dbo.MyView;. Notice it returns results. If you also try SELECT * FROM dbo.MyView2 WITH (NOLOCK); You'll also get results. Try SELECT * FROM dbo.MyView2;, however, and the query will "hang".

You can then "clean up" by returning to your original query window and running the following:

COMMIT;
GO

DROP VIEW dbo.MyView2;
DROP VIEW dbo.MyView;
DROP TABLE dbo.MyTable;

Of course, the real question is, do you need NOLOCK, but that isn't what this question is about.

TSQL NOLOCK VIEW and stored procedure

See the answers to this SO question. To quote:

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."

SQL Server NOLOCK and joins

I won't address the READ UNCOMMITTED argument, just your original question.

Yes, you need WITH(NOLOCK) on each table of the join. No, your queries are not the same.

Try this exercise. Begin a transaction and insert a row into table1 and table2. Don't commit or rollback the transaction yet. At this point your first query will return successfully and include the uncommitted rows; your second query won't return because table2 doesn't have the WITH(NOLOCK) hint on it.

Does NOLOCK hint slow down operation?

The short answer to the question as stated is: "No."

In most cases the NOLOCK hint will speed up the query in question, as well as, any other queries operating against the specified table at the same time. The reason is that no locks are checked or obtained. You've listed the possible side effects in your question so I won't cover those here.

At the end of the day the query will be faster, but the results will be suspect.

---

Related Topics