How to Force Nolock Hint for SQL Server Logins

How to force nolock hint for sql server logins

This is a painful and hacky way to do it, but it's what we're doing where I work. We're also using classic asp so we're using inline sql calls. we actually wrap the sql call in a function (here you can check for a specific user) and add "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" to the beginning of the call.

I believe functionally this is the same as the no lock hint. Sorry I don't have a pure SQL answer, I'd be interested to hear if you find a good way to do this.

SQL Server - how to set (nolock) hint as a default?

found this....

How to force nolock hint for sql server logins

seems like the best way to achieve this is to issue a

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

any other idea???

ps

another related question...

NOLOCK vs. Transaction Isolation Level

Grant ReadOnly with (nolock) permission in SQL Server

If you are using Enterprice edition, you can try using resource governor. Set maximum time for query to execute. That is not really what you asked for, but it would solve problem with never ending queries. You can have a look here (scenario 2).

And you can also look at direction of changing database properties (Using Snapshot Isolation)- and this is probably why in Oracle you have not experienced such problems- Oracle uses this by default, but for SQL Server it relatively new feature (>=2005). Should be careful though, because it affects tempdb.

Select with (nolock)

Nolocks should be used with extreme caution. The most common understanding of nolock (read uncommitted) hint is that it reads data that has not been committed yet. However, there are other side effects that can be very dangerous. (search for "nolock" and "page splits")

There's a really good write up here... https://www.itprotoday.com/sql-server/beware-nolock-hint

In short, "nolocking"ing everything is not always a good idea... if ever.

Using WITH NOLOCK Table Hint in Query Using View - Does it Propagate Within the View?

Yes, NOLOCK will propagate to the tables used by the view definition (at least in SQL Server 2005).

See Table Hints in MSDN:

In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

However,

If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

If you're using indexed views you might want to read a bit more as there are some special cases there too.

Also see View Resolution for more info.

Is the NOLOCK (Sql Server hint) bad practice?

With NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.

This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.

I suggest reading When Snapshot Isolation Helps and When It Hurts - the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.

Sql Server permission for read-only user to prevent locks

I guess you could create a view over the table in question and add NOLOCK to that query. Then give their user account read only access to the view rather than the base table?

CREATE VIEW dbo.ReportingView
AS

SELECT COL1, COL2, COL3

FROM dbo.BASETABLE (NOLOCK)

How to put NO LOCK for stored procedure T-SQL for all tables

You can set this at the query level:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Like NOLOCK, this allows for dirty reads.

Is there a way to force Report Builder to use WITH (NOLOCK) in the queries it generates?

Create views as data source for the report, and add with (nolock) to all tables in the view's select statement.

---

Related Topics