With (nolock) cannot be used in CLR table valued function in SQL Server

Time:2021-1-8

staySQL ServerCommon table valued functions(table-valued function)Yes, you can use table tips(Hints-Table)Yes, thenCLRTable valued functions of type(table-valued function)Can table prompts also be used(Hints-Table)What about it? I believe many people have not paid attention to this problem.

 

Next, I’ll takeYourSQLDbaReady made inCLRTable valued functions to illustrate this problem. If you have a customCLRTable valued function, you can also construct their own experiments, viewCLRTable valued functions can use the followingSQL

 

 

SELECT * FROM sys.objects WHERE type=‘FT’

 

 

As shown below,CLRTable valued functions cannot be usedWITH(NOLOCK)Yes. It will prompt for syntax errors.

 

USE YourSQLDba;
GO
SELECT  *
FROM    [yUtl].[clr_GetFolderList]('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA',
                                   '*.mdf') WITH(NOLOCK)
 
Msg 319, Level 15, State 1, Line 43
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.

 

 

clip_image001

 

 

Now I create a view as follows:

 

 
CREATE VIEW yUtl.v_test
AS
    SELECT  *
    FROM    [yUtl].[clr_GetFolderList]('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\',
                                       '*.mdf')
 
 
 
 
SELECT * FROM yUtl.v_test WITH(NOLOCK);

 

 

 

clip_image002

 

Msg 4139, Level 16, State 1, Line 52

Cannot process the query because it references the common language runtime (CLR) table-valued function “yUtl.clr_GetFolderList” with a hint through view “yUtl.v_test”.

 

 

 

In fact, this is because the view object usesWITHNOLOCK)In this case, it will be automatically converted to addingWITH(NOLOCK)If such a view is called in a stored procedure, the following error message will appear

 

 

Cannot process the query because it references the common language runtime (CLR) table-valued function “xxxx” with a hint through view “xxxx”.

 

But it’s a bit strange that the official information doesn’t specify itCLRTable valued functions cannot be usedWITH(NLOCK)It is not clear whether the document is not perfect or the details are ignored. This is the first time that I have encountered this kind of mistake. I would like to record this problem.

 

 

reference material:

 

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15