[SOLVED] Why is WITH required when using NOLOCK on a remote table call?

Why is WITH required when using NOLOCK on a remote table call?

I ran into an issue today trying to access a remote table using NOLOCK. I received this error:

Remote table-valued function calls are not allowed.

Upon Googling the issue, I found that adding WITH (NOLOCK) corrects the issue. I'm wondering why that is?

SQL:

SELECT *
FROM [LINKED_SRV].[DB1].[dbo].[REMOTE_TABLE] WITH (NOLOCK)

Solution

Quoting the documentation for table hints:

Important

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.

The documentation states that for your attempted query, omitting the WITH keyword is allowed. Based on that, I would consider this a bug. However, the documentation also states that this feature is deprecated and will be removed in a future version of SQL Server, so you should not expect this bug to be fixed in any other way than by a change to the documentation.

I suspect that the parser sees FROM server.database.schema.table ( and decides that this is a call to a user-defined function, therefore an error, and never gets to the point where it sees that NOLOCK is not a function argument.