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?
Best Answer
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 USEROPTIONSand checking the value forisolation 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