SQL Server 2005 - provide NoLOCK on Database level

Just like NOLOCK option applied for a single table to get data without applying any locks on the table, I need to find if there is some option available on the database level which makes all its "select" automatically with NOLOCK.

Also SET TRANSACTION ISOLATION LEVEL will not work here as that has to be done on all the SPs. I need it on database level.

Thanks
Vipin
LVL 3
vipin_nagarroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this does not exist on database/server/login level.

the only thing that exists is that if the database is marked read-only, there won't be any locks at all on the entire database...

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
The only thing that can be done at database level is

ALTER DATABASE ur_dbname SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ur_dbname SET READ_COMMITTED_SNAPSHOT  ON;
0
vipin_nagarroAuthor Commented:
@angel: Thanks for reply
Is there any way that database is readonly for some login and coulb be insert able for others. I mean can I made that distinguish in application level by connecting with one user for read only mode and with other in normal mode.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I mean can I made that distinguish in application level by connecting with one user for read only mode and with other in normal mode.

Then you need to execute the SET TRANSACTION ISOLATION LEVEL statement at the beginning of the session for those users alone..
0
vipin_nagarroAuthor Commented:
@rrjegan17: How could this be best implemented in existing applications. I have around 10 applications accessing a particular db and one application is inserting the realtime data.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Do you have any user levels or user based permissions available in your application. If so, then you would be passing user permissions or privileges across pages in a session and make use of that. Just use that info for this validation..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vipin_nagarroAuthor Commented:
Thanks, u helped me out
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
welcome..
And kindly let me know the reason for a C grade for this question..
More on grading here:

http://www.experts-exchange.com/help.jsp#hs=29&hi=403

"What's the right grade to give?

Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.

Giving a higher grade has no impact on your Available Points."
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.