What is the READPAST Query Hint?

Have you ever heard of the READPAST query hint? I would say it’s kind of a sibling to NOLOCK. With NOLOCK, you’ll get data back faster but risk dirty reads. With READPAST, SQL Server will skip right past locked data altogether. This can either be helpful or, if you don’t know how it’s used, incredibly dangerous.

Let’s compare NOLOCK and READPAST so you know what to expect if you want to use these query hints properly.

Hurry Up and Wait

Let’s use the VoteTypes table in the StackOverflow2013 database for this example. We can see that “Offensive” corresponds to Id 4:

Let’s update that Name to say “Rude” instead. We’ll start a transaction and make our update in one window. Before we rollback or commit, we’ll try to select the VoteTypes table in another window:

We’re going to be waiting on our SELECT until the first query is either rolled back or committed. In this case, we’ll roll it back:

As soon as we roll back, we have the results of our SELECT.

Sibling Rivalry

Let’s repeat the scenario again, but add the NOLOCK hint:

In this case, our SELECT completes almost instantly, and we see the table includes our change from “Offensive” to “Rude.” With our transaction still open on the left, let’s try using the READPAST hint instead of NOLOCK:

Do you see the difference? Our query still completed almost instantly, but since Id 4 was being updated in the left window, our query in the right window read past Id 4. We went from Id 3 to Id 5, as if Id 4 doesn’t even exist.

Handle with Care

Everybody wants their queries to be faster. READPAST can make queries faster, but it’s at the expense of accuracy. It can be dangerous if you end up skipping rows you care about. Don’t let somebody slip this in, thinking they’re making improvements. Choose wisely if you decide to put it to use.

Thanks for reading!