使用视图和表值函数作为匹配子句中的节点或边缘表 [英] Use views and table valued functions as node or edge tables in match clauses

问题描述

我喜欢使用 Table Valued functions MATCH 子句的方式与 Node 表。有没有办法实现这一点？

需要表值函数

可以有各种用例将表值函数或视图用作 Node 表。例如mine是以下内容。

我有 Node 包含 NVarChar（max）我想要搜索字面文本的字段。我只需要进行平等搜索并且不需要全文搜索，因此我选择在散列值的文本字段。正如 Remus Rusanu 在他的汇集到 SQL服务器 - 值得索引大字符串键？和 https：// www。 brentozar.com/archive/2013/05/indexing-wide-keys-in-sql-server/ 。表值函数使用 CHECKSUM 索引;请参阅消息207对于内联表值函数中的伪列名称$ node_id无效。

示例数据定义

  CREATE TABLE [Tags]（
 [tag] NVarChar（max） ，
 [tagHash] AS CHECKSUM（[Tag]）PERSISTED NOT NULL 
）as Node; 
 
 CREATE TABLE [站点]（
 [endPoint] NVarChar（max），
 [endPointHash] AS CHECKSUM（[endPoint]）PERSISTED NOT NULL 
）as Node ; 
 
 CREATE TABLE [Links] as Edge; 
 
 CREATE INDEX [IX_TagsByName] ON [Tags]（[tagHash]）; 
 
 GO 
 
 CREATE FUNCTION [TagsByName]（
 @tag NVarChar（max））
 RETURNS TABLE 
 WITH SCHEMABINDING 
 AS 
 RETURN SELECT 
 $ node_id AS [NodeId]，
 [tag]，
 [tagHash] 
 FROM [dbo]。[标签] 
 WHERE [tagHash] = CHECKSUM（@tag）AND 
 [tag] = @tag; 
  

[TagsByName] 返回 NodeId 的Microsoft Docs> $ node_id ，如https://stackoverflow.com/a/45565410/814206 。但是，真正 Node 表包含两个内部列，我不知道如何导出。

所需的查询

我想查询类似于以下的数据库：

  SELECT * $ b $ FROM [TagsByName]（'important'）as t，
 [Sites] as s，
 [Links] as l 
 WHERE MATCH（[t]  - （[l]） - > [s]）
  

然而，导致错误 ¹ ：

Ms 13901，Level 16，State 2，Line ... >
MATCH 子句不是 node table 或节点表的别名。

我有办法做到这一点吗？

---

PS。有一些解决方法，但它们看起来不像 MATCH -query;特别是考虑到我的实际查询涉及匹配更多的关系和更多的字符串相等性测试，我会将这些解决方法作为答案发布，并希望有人带来更好的想法。 $ b

¹ _{这给出了视图之间的区别和表中的sql ;这只发生在 sql-server-2017 ，仅当使用SQL Graph时。}

解决方案

解决方法

向from子句添加两次 Node 表：一次表一次作为表值函数，并通过where子句中的 $ node_id 加入它们：

  SELECT * 
 FROM [TagsByName]（'important'）as t1，
 [Tags] as t2，
 [Sites] as s，
 [Links ]作为l 
 WHERE MATCH（[t2]  - （[l]） - > [s]）AND 
 t1。[NodeId] = t2。$ node_id 
  

这会影响效果吗？

I like to use Table Valued functions in MATCH clauses in the same way as is possible with Node tables. Is there a way to achieve this?

The need for table valued functions

There can be various use cases for using table valued functions or views as Node tables. For instance mine is the following.
I have Node tables that contain NVarChar(max) fields that I would like to search for literal text. I need only equality searching and no full text searching, so I opted for using a index on the hash value of the text field. As suggested by Remus Rusanu in his answer to SQL server - worth indexing large string keys? and https://www.brentozar.com/archive/2013/05/indexing-wide-keys-in-sql-server/. A table valued function handles using the CHECKSUM index; see Msg 207 Invalid column name $node_id for pseudo column in inline table valued function.

Example data definitions

CREATE TABLE [Tags](
    [tag] NVarChar(max),
    [tagHash] AS CHECKSUM([Tag]) PERSISTED NOT NULL
) as Node;

CREATE TABLE [Sites](
    [endPoint] NVarChar(max),
    [endPointHash] AS CHECKSUM([endPoint]) PERSISTED NOT NULL
) as Node;

CREATE TABLE [Links] as Edge;

CREATE INDEX [IX_TagsByName] ON [Tags]([tagHash]);

GO

CREATE FUNCTION [TagsByName](
    @tag NVarChar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT
    $node_id AS [NodeId],
    [tag],
    [tagHash]
FROM [dbo].[Tags]
WHERE [tagHash] = CHECKSUM(@tag) AND
    [tag] = @tag;

[TagsByName] returns the $node_id with an alias NodeId as suggested by https://stackoverflow.com/a/45565410/814206. However, real Node tables contain two more internal columns which I do not know how to export.

Desired query

I would like to query the database similar to this:

SELECT *
FROM [TagsByName]('important') as t,
    [Sites] as s,
    [Links] as l
WHERE MATCH ([t]-([l])->[s])

However, this results in the error¹:

Msg 13901, Level 16, State 2, Line ...
Identifier 't' in a MATCH clause is not a node table or an alias for a node table.

I there a way to do this?

---

PS. There are some workarounds but they do not look as elegant as the MATCH-query; especially considering that my actual query involves matching more relations and more string equality tests. I will post these workarounds as answers and hope that someone comes with a better idea.

¹ _{This gives a very specific difference between views and tables for Difference between View and table in sql; which only occurs in sql-server-2017 and only when using SQL Graph.}

解决方案

Workaround

Add the Node table twice to the from clause: once as table and once as table valued function and join them via the $node_id in the where clause:

SELECT *
FROM [TagsByName]('important') as t1,
    [Tags] as t2,
    [Sites] as s,
    [Links] as l
WHERE MATCH ([t2]-([l])->[s]) AND
    t1.[NodeId] = t2.$node_id

Does this affect performance?

这篇关于使用视图和表值函数作为匹配子句中的节点或边缘表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！