SET TRANSACTION ISOLATION LEVEL cannot be executed in a UDF (SQL Server function), but can be applied with the use of TABLE HINTS.SQL Server Locking and row versioning are central to isolation levels

SET TRANSACTION ISOLATION LEVEL controls the locking levels and row versioning characteristics of DML statements.

Locking levels and row versioning methods are used to maintain integrity of transactions.

SQL Row versioning maintains different versions of rows during a transaction. During a transaction, rows from the transaction start are available. Row versioning decreases the possibility of locking.

Locking levels are applied at a row, page or table level. They are applied in a way that minimises problems for the current transaction. The relevant locks stop other transactions from accessing data, when the current transaction completes the locks are released.

An application can choose an isolation level – defining the levels of access another transaction has on the current data. The choice of isolation level defines the row versioning and locking levels

The isolation levels available are: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE

Firstly, only one isolation level can be set per connection – unless changed .

Secondly, a UDF can’t execute the SET TRANSACTION ISOLATION LEVEL. The alternative for a UDF is a table hint. The table hint overrides the optimizer choice for the duration of the DML. An example of a table hint is:

USE MyDB
GO

SELECT col1,col2 FROM myTable WITH (TABLOCK)
WHERE col1 = ‘a_value’
GO

Locks on resources need to be managed effectively. An interesting by-product of locks is SQL Server SLEEPING MODE, locks and transactions

Although this restriction applies to a SQL Server UDF, the TABLE HINTS offer sufficient flexibility to achieve a similar approach

Republished from http://www.sqlserver-dba.com.