Do I need NO LOCK in my CREATE View query

Question

I am creating a view on a MS SQL Server. I have not had much exposure to MS SQL and am not real familiar with the NO LOCK hint. I understand what it does, but I don't know if I need to use it in my situation. I have been asked if I should include it and I don't know.

Do I need to add NO HINT after all the queries I am using to create my view? Or will that have any affect on the user querying the view itself? Should the USER add the NO LOCK to the query against the VIEW?

Any guidance on the best approach and any clarification is appreciated!

Answer 1

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 Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

Answer 2

Straight from the Doc:

Caution: Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

The NOLOCK hint in particular is a notoriously pernicious and bad idea. It should only be used for special circumstances and specific needs: Such as when you are not concerned if the data returned is correct.