Msg 207 Invalid column name $node_id for pseudo column in inline table valued function

Question

In a Node-table the pseudo column name $node_id refers to the internal name of the node id column and using the pseudo column is recommended (see SQL Graph Architecture §Node Table).

For example after creating the following table:

create table [Sites](
[SiteName] NVarChar(max) NOT NULL,
[EndPoint] NVarChar(max),
[SiteNameHash] as CheckSum([SiteName]) PERSISTED NOT NULL,
[EndPointHash] as CheckSum([EndPoint]) PERSISTED NOT NULL,

INDEX IX_Sites_NodeId UNIQUE CLUSTERED ($node_id),
INDEX IX_Sites_SiteName UNIQUE NONCLUSTERED (SiteNameHash, $node_id),
INDEX IX_Sites_EndPoint UNIQUE NONCLUSTERED (EndPointHash, $node_id))

as Node;

The query:

SELECT $node_id
  ,[SiteName]
  ,[EndPoint]
  ,[SiteNameHash]
  ,[EndPointHash]
FROM [EmersonAnalysis].[dbo].[Sites]
where $node_id =  '{"type":"node","schema":"dbo","table":"Sites","id":0}'

uses the pseudo column $node_id in the SELECT-clause and in the WHERE-clause to select a single node (if it exists).

However, on the following table valued function:

create function SitesByName(
    @sitename as NVarChar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS
    return select
        $node_id,
        [SiteName],
        [EndPoint],
        [SiteNameHash],
        [EndPointHash]
    from [dbo].[Sites]
    where [SiteNameHash] = CHECKSUM(@sitename) AND
        [SiteName] = @sitename;

The similar query:

select
    fn.$node_id
from [Sites]
outer apply SitesByName([SiteName]) as fn

Results in the error message:

Msg 207, Level 16, State 1, Line 2 Invalid column name '$node_id'.

Is it possible to use pseudo column names when selecting columns from functions? If so, how can I use pseudo column names?

PS. I'm using sql-server-2017 RC 2 v14.0.900.75.

Answer 1

Workarround: you can define an alias for $node_id in the table valued function and use that in your select instead of the pseudo column:

ALTER function [dbo].[SitesByName](
    @sitename as NVarChar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS
    return select
        $node_id [NodeId],
        [SiteName],
        [EndPoint],
        [SiteNameHash],
        [EndPointHash]
    from [dbo].[Sites]
    where [SiteNameHash] = CHECKSUM(@sitename) AND
        [SiteName] = @sitename;

If fact SSMS displays a red squiggly line (likely an error or warning) under [SitesByName] with the text:

No column was specified for column '1' of 'SitesByName'