8

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?

Improve this question
2

1 Answer 1

Reset to default
10

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

Improve this answer
4
  • Thanks for confirming this. Shame it's not possible the way I'd prefer to have it.
    – Jeroen
    Jul 10, 2014 at 15:57
  • @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.
    – Aaron Bertrand
    Jul 11, 2014 at 1:56
  • 1
    @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.
    – Jeroen
    Jul 11, 2014 at 6:14
  • 1
    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.
    – binki
    Jun 11, 2015 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.