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?