Every SQL Server developer has heard it: "Don't use NOLOCK in production!" But this common rule isn't as simple as it seems. Sometimes, what seems like a bad practice can actually be the right choice.

Let's explain what NOLOCK really does in simple terms. When you use NOLOCK, you're telling SQL Server "just show me what's there right now, even if it might change in a second." you could compare it to looking at your bank account while transactions are still processing - the numbers you see might not be final, but you get them right there and then.

Here's a simple query that might cause problems under heavy load:

SELECT OrderDate, COUNT(*) as OrderCount, SUM(OrderTotal) as DailyTotal
FROM Sales.Orders
GROUP BY OrderDate
ORDER BY OrderDate DESC

This query calculates how many orders were placed each day and the total revenue for those orders. Without NOLOCK, it waits for any other updates or inserts to finish before it can get the data, which could slow things down when the database is busy.

By adding NOLOCK, we can prevent blocking:

SELECT OrderDate, COUNT(*) as OrderCount, SUM(OrderTotal) as DailyTotal
FROM Sales.Orders WITH (NOLOCK)
GROUP BY OrderDate
ORDER BY OrderDate DESC

Adding WITH (NOLOCK) tells SQL Server, "Don’t wait for updates or other locks on this data – just show me what’s there." This means the query runs faster but might return slightly out-of-date information.

Let's think up an example. Think of a reporting system that's driving users crazy. The reports are perfectly accurate, but they take so long to run that people stop using them. When you look at the problem, it turns out each report is waiting for other processes to finish before it can read any data. Adding NOLOCK to some of the report queries changes everything - reports that took five minutes now finish in seconds.

Here's a real example showing the impact:

CREATE PROCEDURE dbo.GetDashboardStats
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT 
(SELECT COUNT(*) FROM Orders WITH (NOLOCK) 
WHERE OrderDate = CAST(GETDATE() AS DATE)) as TodayOrders,
(SELECT SUM(OrderTotal) FROM Orders WITH (NOLOCK)
WHERE OrderDate = CAST(GETDATE() AS DATE)) as TodayRevenue,
(SELECT COUNT(DISTINCT CustomerID) FROM Orders WITH (NOLOCK)
WHERE OrderDate = CAST(GETDATE() AS DATE)) as UniqueCustomers
END

This stored procedure runs three separate queries to get today’s order count, total revenue, and number of unique customers. WITH (NOLOCK) ensures that these queries don’t wait for any ongoing changes in the Orders table, allowing the report to generate much faster.

Sure, the numbers could be slightly off, but by the time anyone acts on those reports, the data would have changed anyway.

This brings up interesting questions about database design. We often chase perfect accuracy without asking if we really need it. Take any e-commerce site really. When showing how many items are in stock, what's more important - having the exact number right now, or having a page that loads quickly? Most customers would rather see "approximately 100 items left" right away than wait several seconds for "exactly 98 items."

Here's how you might handle it with multiple tables:

SELECT c.CustomerName, 
COUNT(o.OrderID) as OrderCount,
SUM(od.Quantity) as TotalItems
FROM Customers c
JOIN Orders o WITH (NOLOCK) ON c.CustomerID = o.CustomerID
JOIN OrderDetails od WITH (NOLOCK) ON o.OrderID = od.OrderID
WHERE o.OrderDate >= DATEADD(day, -30, GETDATE())
GROUP BY c.CustomerName

In this query, we are retrieving customer names along with their total order count and the number of items they ordered in the last 30 days. Using NOLOCK on the Orders and OrderDetails tables prevents blocking if those tables are being updated at the same time, improving the speed of the query.

Notice NOLOCK is only on the tables that change frequently - a strategic choice that balances speed and accuracy.

But let's be clear - there are times when you absolutely need accurate reads. Processing payments? You need exact numbers. Updating inventory during checkout? You can't use NOLOCK there. Here's what not to do:

-- Never do this!
UPDATE Inventory WITH (NOLOCK)
SET StockCount = StockCount - 1
WHERE ProductID = @ProductID
AND StockCount > 0 -- This check becomes unreliable with NOLOCK

Trying to update inventory with NOLOCK can lead to unreliable results. The check on StockCount > 0 might give incorrect information because NOLOCK allows reading "dirty" data, meaning you could reduce the stock count even if there’s not enough stock.

Instead, use proper transaction handling:

BEGIN TRANSACTION
UPDATE Inventory 
SET StockCount = StockCount - 1
WHERE ProductID = @ProductID
AND StockCount > 0
IF @@ROWCOUNT = 1
COMMIT
ELSE
ROLLBACK

Here, we use a transaction to ensure that the stock count is updated safely and correctly. If the update is successful (i.e., there’s enough stock), the changes are committed; if not, they are rolled back, preventing any errors.

The real skill is knowing when perfect accuracy matters and when it doesn't. Reports and displays often don't need perfect accuracy. If a dashboard shows 1,002 sales today instead of the actual 1,003, will that really change any business decisions?

Want to measure the actual impact? Here's a simple test:

DECLARE @StartTime datetime = GETDATE()
-- Your query here with NOLOCK
SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) as ExecutionTime

This script measures how long a query takes to execute by calculating the time difference between the start and end of the query. Running this during peak hours can help you see the real difference that NOLOCK makes.

Teams might spend weeks optimizing queries to be perfectly accurate, only to find that users care more about speed than precision. It's just like insisting on counting every grain of rice when the customer just wants to know if they have enough for dinner. 

Here's a key point: databases exist to solve business problems. Sometimes a fast answer that's mostly right is better than a slow answer that's perfectly right. It's not about cutting corners - it's about understanding what users actually need. Think about it this way: if a "correct" solution makes your system so slow that people avoid using it, is it really correct? Being pragmatic often leads to better results than being perfect.

The key is to think about each situation separately. Don't just follow rules blindly. Ask yourself: "What matters more here - getting exact numbers, or getting answers quickly?" Often, that simple question will guide you to the right choice.

At the end of the day, we're building systems for real people to use. If NOLOCK helps them do their jobs better, maybe it's not so dirty after all.