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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2005-07-13 : 11:30:48
|
| Hi,I remember reading before, on a way you could set NOLOCK on an entire stored procedure without having to add NOLOCK to every single table. You could declare something at the top of the sproc. Does this ring any bells?Also, I'm curious how often people use NOLOCK. I know it is more efficient but do most people use it on their SELECT statements? I'm thinking about adding it to a number of our sprocs to potentially speed things up and I was curious if people had an strong opinions either way. ThanksNic |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-13 : 11:42:16
|
| --to set "nolock" for entire SPset transaction isolation level read uncommittedWhat's important is that you understand what it does. I use it a lot in specific situations. ie: For search queries that use the same tables that an OLTP system writes to. In our case, we don't have a problem with dirty reads and we don't want to block users from writing to these tables.Be One with the OptimizerTG |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2005-07-13 : 12:41:08
|
| Thanks,I just want to clarify my scenario to see if the no locking would help. We have an application where a user will query the database for a set of records. In most scenarios, this user will be the only one accessing this particular record. (The record consists of mulitple tables etc but is one subset of data). There are some queries where the user can "Search" for a group of records. In this scenario, multiple records are returned. Dirty reads for the "Search" isn't really an issue and in the first scenario, since only one person should be accessing that record at a time, dirty reads should not be a problem. Would not locking the tables help improve effeciency in this scenario?ThanksNic |
|
|
|
|
|
|
NOLOCK syntax