I have a few views that do joins between multiple tables and in the view definition I use the WITH NOLOCK command but should I also use it when I issue the select against the view? I.E. Select * from View WITH (NOCLOCK)?
-- If I get used to envying others... Those things about my self I pride will slowly fade away. -Stellvia
My understanding was it was bestpractice to use when the DB has a lot of read activity. There is also random INSERT/UPDATEs happening to it that could be at the same time as a SELECT.
-- If I get used to envying others... Those things about my self I pride will slowly fade away. -Stellvia
It's not a "best practice", more like a last resort. Unless you've conducted tests with and without the hint that show it to be necessary you are better off not using it. And if hints must be used they should be applied on a query-by-query basis, not as part of a view definition.
If you have contention on the underlying tables, consider SNAPSHOT isolation instead.
I have a few views that do joins between multiple tables and in the view definition I use the WITH NOLOCK command but should I also use it when I issue the select against the view? I.E. Select * from View WITH (NOCLOCK)?
-- If I get used to envying others... Those things about my self I pride will slowly fade away. -Stellvia
No need to use the hint when selecting from the view.
Using WITH NOLOCK on views
Posted - 06/30/2012 : 14:21:52
