We are having some locking problems with our database and are planning to implement the WITH (NOLOCK) to help with this problem.
We have some questions that we would appreciate your help with:
1) Is the default on 'SELECT * FROM x' to lock each row?
2) Should all SELECTS have WITH (NOLOCK)?
3) Should all INNER JOINS have WITH (NOLOCK) if they are not part of an UPDATE?
4) Will performance improve using WITH (NOLOCK)s?
5) Is the default on 'UPDATE...' to lock each row (ROWLOCK)?
6) Will performance improve using WITH (ROWLOCK)?
7) Can we or should we include WITH (NOLOCK) on temp files?
Thanks in advance.