Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Please always use the schema prefix - especially with functions. –  Aaron Bertrand Jul 11 at 1:59
2  
    
@AaronBertrand Fair point about the schemas, I've updated the question. Also thanks for the link to the SO thread (perhaps it would even serve as a good addition to MarkSinkinson's answer?). –  Jeroen Jul 11 at 6:22

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
Thanks for confirming this. Shame it's not possible the way I'd prefer to have it. –  Jeroen Jul 10 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 at 1:56
    
@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 at 6:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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