Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2012-06-30 : 14:21:52
|
| 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 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-30 : 14:28:31
|
| Why are you using NOLOCK in the first place? |
 |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-06-30 : 15:05:21
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-30 : 19:16:42
|
| 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. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-07-02 : 13:07:54
|
quote:
Originally posted by Eagle_f90
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