High Performance SQL Views Using WITH(NOLOCK)

Every now and then you find a simple way to make everything much faster.  We often find customers creating data warehouses or OLAP cubes even though they have a relatively small amount of data (a few gigs) compared to their server memory.  If you have more server memory than the size of your database or working set, nearly any aggregate query should run in a second or less.  In some situations there may be high traffic on from the transactional application and SQL server may wait for several other queries to run before giving you your results.

The purpose of this is make sure you don’t get two versions of the truth.  In an ATM system, you want to give the bank balance after the withdrawal, not before or you may get a very unhappy customer.  So by default databases are rightly very conservative about this kind of thing.

Unfortunately this split-second precision comes at a cost.  The performance of the query may not be acceptable by today’s standards because the database has to maintain locks on the server.  Fortunately, SQL Server gives you a simple way to ask for the current version of the data without the pending transactions.  To better facilitate reporting, you can create a view that includes these directives.

CREATE VIEW CategoriesAndProducts AS
SELECT *
FROM dbo.Categories WITH(NOLOCK)
INNER JOIN dbo.Products WITH(NOLOCK) ON dbo.Categories.CategoryID = dbo.Products.CategoryID

In some cases quires that are taking minutes end up taking seconds.  Much easier than moving the data to a separate database and it’s still pretty much real time give or take a few milliseconds. You’ve been warned not to use this for bank balances though.