Can I achieve WITH(NOLOCK) on all joins in a stored procedure with a single command?

I have a number of reporting stored procedures that purely list
records and make no changes to the data. I have noticed that some of
these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
a simple example :-
Select * from table1 WITH(NOLOCK)
INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
There lots of these and many of them have lots of joins so I'm looking
for a way to apply WITH(NOLOCK) to the whole procedure and save myself
the time it takes to add the hint to each table/join. I know that it
is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
volunteer as the deadlock victim, but this isn't suitable as I need
the procedure to return it's records.
Does anybody have a suggestion or am I looking ata couple of days of
ctrl-v'ing WITH(NOLOCK) everywhere?
Thanks,
LiamHow about below?
SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> I have a number of reporting stored procedures that purely list
> records and make no changes to the data. I have noticed that some of
> these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> a simple example :-
> Select * from table1 WITH(NOLOCK)
> INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> There lots of these and many of them have lots of joins so I'm looking
> for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> the time it takes to add the hint to each table/join. I know that it
> is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> volunteer as the deadlock victim, but this isn't suitable as I need
> the procedure to return it's records.
> Does anybody have a suggestion or am I looking ata couple of days of
> ctrl-v'ing WITH(NOLOCK) everywhere?
> Thanks,
> Liam|||Thanks, that's just what I was looking for.
Liam
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uvCEm35nDHA.2312@.TK2MSFTNGP12.phx.gbl>...
> How about below?
> SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Liam Weston" <liam_weston@.hotmail.com> wrote in message
> news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> > I have a number of reporting stored procedures that purely list
> > records and make no changes to the data. I have noticed that some of
> > these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> > a simple example :-
> >
> > Select * from table1 WITH(NOLOCK)
> > INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> > INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> > LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> >
> > There lots of these and many of them have lots of joins so I'm looking
> > for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> > the time it takes to add the hint to each table/join. I know that it
> > is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> > volunteer as the deadlock victim, but this isn't suitable as I need
> > the procedure to return it's records.
> >
> > Does anybody have a suggestion or am I looking ata couple of days of
> > ctrl-v'ing WITH(NOLOCK) everywhere?
> >
> > Thanks,
> >
> > Liam

Can a View be protected to support "JOINS" only?

I know this is a way-out question, and I'm not confident of a positive
answer, but I'd like to know whether it is possible to protect/secure a View
so that it can only be used as a TARGET in a join operation, as opposed to
being used for direct SELECT.

for example:

SELECT * FROM MyView -- would fail

but

SELECT * FROM Customer c
LEFT OUTER JOIN MyView v ( on v.Key = c.ViewKey ) -- would succeed

The question is predicated on an idea that sometimes data is permissable to
access when presenting it as "secondary data" because we are accessing
individual "points" of data, but we don't want people accessing the entire
data-set.

I know that the JOINed query above could be made into another View, bypass
MyView altogether (simply reference the underlying table), in which case
MyView becomes superfluous and the desired outcome is achieved.

Except that: depending on the combinations of JOINS, and if there are
multiple of them, I might end up with a large number of views to allow for
all the combinations of different joins that I want. For example, a table
with 6 foreign keys has 64 different combinations to cover the different
joins I might or might not want to make. A table with 10 foreign keys takes
that up to 1024 combinations etc.Kevin Frey (kevin_g_frey@.hotmail.com) writes:

Quote:

Originally Posted by

I know this is a way-out question, and I'm not confident of a positive
answer, but I'd like to know whether it is possible to protect/secure a
View so that it can only be used as a TARGET in a join operation, as
opposed to being used for direct SELECT.
>
for example:
>
SELECT * FROM MyView -- would fail
>
but
>
SELECT * FROM Customer c
LEFT OUTER JOIN MyView v ( on v.Key = c.ViewKey ) -- would succeed
>
The question is predicated on an idea that sometimes data is permissable
to access when presenting it as "secondary data" because we are
accessing individual "points" of data, but we don't want people
accessing the entire data-set.

Consider:

SELECT v.*
FROM MyView v
JOIN dummytbl d ON 1 = 1

And if dummytbl is a one-row table, you still get all rows from MyView.

I find it difficult to achieve what you are looking for without writing
custom query tool where the users only can build queries in some guided
way, and where you disallow certain constructs or impose a TOP clause
to the queries.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx