Lesson 48. A Better Alternative To WITH (NOLOCK)

There is a better way to get dirty reads in SQL Server than littering your code with WITH (NOLOCK) code hints.

Here is a good way to make yourself stand out from other job candidates. When the interviewer asks you how to pull data without disturbing users, 90% of applicants will say WITH (NOLOCK). It is the book answer. However, in the real world, analytic SQL statements can entail dozens of tables. Having to put WITH (NOLOCK) everywhere is annoying and makes the code unreadable. Instead, you can tell your entire script to accept dirty reads by setting the transaction isolation level globally throughout the script.

Examples

Now Isn’t This Much Better?

In [ ]:

USE AdventureWorks2016

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT *
FROM [Sales].[SalesOrderHeader] soh
JOIN [Sales].[SalesOrderDetail] sod
ON soh.SalesOrderID = sod.SalesOrderID

SET TRANSACTION ISOLATION LEVEL READ COMMITTED