SQL Server中CLR表值函數(table-valued function)不能使用WITH(NOLOCK)

在SQL Server中，普通的表值函數（table-valued function）是可以使用表提示（Hints-Table）的，那麼CLR類型的表值函數（table-valued function）是否也可以使用表提示（Hints-Table）呢？ 相信很多人都沒有留意過這個問題。

下麵我以YourSQLDba中現成的CLR表值函數來演示一下這個問題。如果自己手頭有自定義CLR表值函數的，也可以構造自己的實驗，查看CLR表值函數可以使用下麵SQL

SELECT * FROM sys.objects WHERE type='FT'

如下所示，CLR表值函數是不能使用WITH(NOLOCK)的。它會提示語法錯誤。

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.

下麵我創建了一個視圖，如下所示：

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);

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".

其實這個是因為視圖對象使用了WITH（NOLOCK）的話，就會自動轉換為給視圖內部對象加上WITH(NOLOCK)，那麼就會出現上面的錯誤，如果像存儲過程裡面調用了這樣的視圖，就會出現下麵這樣的錯誤信息

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

但是有點奇怪的是官方資料上並沒有詳細說明CLR表值函數不能使用WITH(NLOCK)等表提示，不清楚是文檔不夠完善，還是忽略了這些細節。個人倒是第一次遇到這種錯誤，特此記錄一下這個問題。

參考資料：

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