Is there an option to declare one time at the beginning of the SP that
all the selects will run with NOLOCK command instead of writing if
after each table name?

Thanks.

omtechguy wrote:
> Is there an option to declare one time at the beginning of the SP that
> all the selects will run with NOLOCK command instead of writing if
> after each table name?
>
Really? You think this might be a good idea? I don't think it is possible
per se, but you should probably investigate using snapshot isolation if
using SQL 2005 or later.

omtechguy wrote:
> 
> Is there an option to declare one time at the beginning of the SP that
> all the selects will run with NOLOCK command instead of writing if
> after each table name?
> 
> Thanks.

Well, you can SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the
beginning of the SP. That will apply this transaction isolation level
wherever possible during the scope of the SP.

Please note that you should only do this if you are sure that there are
no data modifications done on those tables during your transactions.
-- 
Gert-Jan

On 22 =D7=A4=D7=91=D7=A8=D7=95=D7=90=D7=A8, 20:31, Gert-Jan Strik
<sorrytoomuchspamalre...@xs4all.nl> wrote:
> omtechguy wrote:
>
> > Is there an option to declare one time at the beginning of the SP that
> > all the selects will run with NOLOCK command instead of writing if
> > after each table name?
>
> > Thanks.
>
> Well, you can SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the
> beginning of the SP. That will apply this transaction isolation level
> wherever possible during the scope of the SP.
>
> Please note that you should only do this if you are sure that there are
> no data modifications done on those tables during your transactions.
> --
> Gert-Jan

But will is affect only select queries or other too?

omtechguy (omtechguy@gmail.com) writes:
> Is there an option to declare one time at the beginning of the SP that
> all the selects will run with NOLOCK command instead of writing if
> after each table name?
 
I will be blunt: you should only use NOLOCK if you have full understanding
of the consequences, and it is obvious that you don't.

When using NOLOCK, you may read uncommitted data, but you may also fail
to read *committed* data. You may get faster execution and less blocking,
but what does that help if you get incorrect results.

Overall, understanding the implications of NOLOCK can be difficult also
for an expert. Not the least if there are updates in the mix.

As Bob said, you should investigate snapshot isolation. You may also
need to review indexing to improve performance and reduce blocking 
that way.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Feb 22, 1:27=C2=A0pm, omtechguy <omtech...@gmail.com> wrote:
> On 22 =D7=A4=D7=91=D7=A8=D7=95=D7=90=D7=A8, 20:31, Gert-Jan Strik
>
>
>
>
>
> <sorrytoomuchspamalre...@xs4all.nl> wrote:
> > omtechguy wrote:
>
> > > Is there an option to declare one time at the beginning of the SP tha=
t
> > > all the selects will run with NOLOCK command instead of writing if
> > > after each table name?
>
> > > Thanks.
>
> > Well, you can SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the
> > beginning of the SP. That will apply this transaction isolation level
> > wherever possible during the scope of the SP.
>
> > Please note that you should only do this if you are sure that there are
> > no data modifications done on those tables during your transactions.
> > --
> > Gert-Jan
>
> But will is affect only select queries or other too?- Hide quoted text -
>
> - Show quoted text -

The TRANSACTION ISOLATION LEVEL is set per connection - this is very
important to understand. Your connection will keep this level unless
another SET statement is issued, or connection is terminated. If you
use pooled connections and don't reset the level before releasing it
back to the pool, then the affected connection will be eventually re-
used by another program or thread etc and results will be messy