Use NOLOCK hint when calling table valued function

Question

Suppose the following function:

CREATE FUNCTION [dbo].[ufnTest]()
RETURNS TABLE
AS RETURN SELECT 1 AS Nr

My actual function will select actual data from many different tables. I (mostly) understand the risks of using nolock hints, and decided that in this case I actually want them. So I try to call above function like this:

SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)

However, this fails with the following message:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The tips for fixing this error are not relevant. Perhaps this table hint isn't available for table valued functions then?

There are several alternatives I'd consider. One is to use the hint on all tables in my select query, but I'd prefer not to as (a) I'll have to remember this whenever I change the function around and (b) it's repetitive. Another alternative would be to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but a downside is that I don't quite know how to set it back to wat it was before (and statements after the select from my TVF should have the original isolation level).

So, I'd prefer to make WITH (NOLOCK) for a select from a table valued function work. Is this possible?

• Aaron Bertrand: Also see this for determining current isolation level.
• Jeroen: @AaronBertrand Thanks for the link to the SO thread (perhaps it would even serve as a good addition to MarkSinkinson's answer?).

Answer 1

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function.

Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

To change this back to the default, you need to find out what isolation level is currently set (before changing it above).

This can be done by running DBCC USEROPTIONS and checking the value for isolation level.

The default tends to be READ COMMITTED, so to change it to that, you'll want to write a statement like the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

• Aaron Bertrand: @Jeroen Why wouldn't you want to set the isolation level once, instead of manually jamming WITH (NOLOCK) on to every entity? The nice thing about using the isolation level setting is that - when you come to your senses and use RCSI instead of dirty reads that don't actually eliminate locks - you change one line of code instead of every single table reference.
• Jeroen: @AaronBertrand The WITH NOLOCK statement on the tvf would allow me to use exactly one code statement, so that would've been preferred (but isnt possible). Using NOLOCK hints on all tables inside the tvf is obviously not an (preferred) option, as I mention in my question. Setting the isolation level is indeed the other alternative, but requires me to set it back to whatever it was (using mentioned DBCC USEROPTIONS or so), resulting in multiple (rather involved) code statements. But I guess it's the best option left.
• binki: Checking and restoring the original isolation level is really dirty and ugly. Perhaps you could instead just put the single query in its own stored procedure as the isolation level setting is scoped to the current procedure and callees.