主页 / dba / 问题 / 245811
Accepted
Pxtl
Asked: 2019-08-21 11:52:36 +0800 CST

如何使索引视图适用于 SQLCLR？

  • 772

在 SQL Server 中，索引视图是一个充满限制的地狱景观。但我需要一个。我有一个格式化程序 SQLCLR 函数，它创建一个域键的美化版本 - 用户希望能够搜索该美化版本的子字符串。所以我需要一个持久计算列或物化视图的全文索引。

但是，格式化程序依赖于存储在多个表中的数据。

所以这适用于视图，但不适用于持久计算列，因为它们无法从多个表中查询。

我的 SQLCLR 方法是精确且确定性的，因此它应该可以在索引视图中使用，但索引视图的索引键中不能有 SQLCLR。

我可以使用 T-SQL 函数重新实现我的格式化程序FORMAT......但FORMAT也与索引视图不兼容。

索引视图可以做任何事情吗？ 曾经？

如果有人可以向我推荐一个关于最佳实践的好文档，我对“使用触发器滚动你自己的物化视图”方法持开放态度。上次我尝试时，它失控了，插入和更新的代码与初始化之间存在大量重复。

是否有某种方法可以在不使用物化/索引视图或持久计算列的情况下对几百万行的计算数据进行高性能文本搜索？

格式化程序不执行数据访问。但是，我需要输入格式化程序以使其有用的数据将来自多个表（具有良好的常规连接），因此我无法使用持久计算列解决此问题。我无法键入 SQLCLR 列，因此无法在全文索引中使用它。

我以为这很简单。持久列和索引视图旨在执行写入计算，并正确实现观察者模式，以便对其依赖关系的更改反映在计算值上。

sql-server materialized-view

2 个回答

  1. Best Answer

    索引视图可以做任何事情吗？曾经？

    当您将索引视图视为一种解决方案时，它实际上有点悲惨，只是发现您的用例遇到了索引视图的众多限制之一（我在看着你，LEFT JOIN）。

    没错，您不能在索引视图的索引键中包含 SQLCLR。但是，您可以将其包含在SELECT视图定义列表中，这会将值保存到磁盘。因此，您至少可以避免在从表中读取时即时计算值的成本。

    在 AdventureWorks2014 示例数据库中，我在命名良好的Person.Person表上创建了这个视图：

    CREATE OR ALTER VIEW dbo.PersonWithHashForSomeReason
WITH SCHEMABINDING
AS
SELECT 
    BusinessEntityID,
    PersonType,
    dbo.SpookyHash(CONVERT(binary(50), FirstName)) AS FirstNameHash
FROM Person.Person
GO

    注意：我懒得写自己的 CLR 函数，所以这个来自这个 Q&A。

    我可以通过将其聚集在以下位置来使其成为索引视图BusinessEntityID：

    CREATE UNIQUE CLUSTERED INDEX CX_BusinessEntityID 
ON dbo.PersonWithHashForSomeReason (BusinessEntityID);
GO

    在表中查找一组特定的行会产生索引扫描（执行计划链接）。请注意缺少Compute Scalar通常用于生成哈希值的运算符。由于哈希在索引视图中被持久化到磁盘，因此没有必要：

    SELECT BusinessEntityID, FirstNameHash 
FROM dbo.PersonWithHashForSomeReason WITH (NOEXPAND)
WHERE FirstNameHash = 0x910C426C533F2C0AAF350158331E3B01;

    Plan Explorer 中的执行计划截图

    我不得不使用NOEXPAND提示让它使用视图。

    您会注意到计划中的警告是由于扫描了整个表以找到这些值，因为没有键控索引FirstNameHash。

    不幸的是，尝试在此持久值上创建非聚集索引失败：

    CREATE NONCLUSTERED INDEX IX_FirstNameHash 
ON dbo.PersonWithHashForSomeReason (FirstNameHash);

    消息 1976，级别 16，状态 1，第 48 行
    无法在视图“dbo.PersonWithHashForSomeReason”上创建索引或统计信息“IX_FirstNameHash”，因为无法验证键列“FirstNameHash”的准确性和确定性。考虑从索引或统计键中删除列，如果计算后将列标记为持久存在于基表中，或者在键中使用非 CLR 派生列。

    这是因为 SQL Server不信任我们：

    为什么我们不信任用户？这里真的出了什么问题？

    假设用户错误地将非确定性函数的自定义属性 IsDeterminsitic 设置为 true，并假设他能够在调用此函数的计算列上创建索引而不持久化它。[...]这可能会导致索引 损坏，因为该函数可能会为相同的输入返回不同的值，因为该函数是不确定的。

    因此，为了用户的安全，此版本中的 Sql Server 要求用户持久保存计算列（与 tsql 情况不同）以实际索引计算列。

    我想全文索引也存在同样的限制，尽管我没有尝试过。

    正如您在自己的帖子中提到的，从这一切中得出的结论是，您有点被困在这里。实际上，您唯一的选择是：

    • 接受表扫描（根据您的描述，不理想，甚至可能不实用），或者
    • 使用触发器“手动”在普通列中保持此值是最新的
    • 4

  2. 所以我需要一个持久计算列或物化视图的全文索引。

    无法在非确定性列上创建全文索引。

    FTS 错误

    Search尝试对索引视图中定义的列进行全文索引时产生了该错误，如下所示：

    CREATE TABLE dbo.T1 (id integer PRIMARY KEY, v1 datetime2(3) NULL);
CREATE TABLE dbo.T2 (id integer PRIMARY KEY, v2 decimal (9, 2) NULL);
CREATE TABLE dbo.T3 (id integer PRIMARY KEY, v3 money NULL);
GO
INSERT dbo.T1 (id, v1) VALUES (1, SYSUTCDATETIME());
INSERT dbo.T2 (id, v2) VALUES (1, 1234567.89);
INSERT dbo.T3 (id, v3) VALUES (1, $987.65);
GO
CREATE VIEW dbo.FormattedData
WITH SCHEMABINDING
AS
SELECT 
    T1.id,
    Search =
        N'Datetime: ' + SQL#.Date_Format(T1.v1, N'D', N'de-de') + N'; ' +
        N'Decimal: ' + SQL#.Math_FormatDecimal(T2.v2, N'###,###,###.00', N'') + N'; ' +
        N'Money: ' + SQL#.Math_FormatDecimal(T3.v3, N'C', N'de-de')
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.id = T1.id
JOIN dbo.T3 AS T3
    ON T3.id = T2.id;

    那里引用的函数来自SQL# CLR 库，并且在免费版本中可用。它们与函数的功能相匹配FORMAT，这在索引视图中根本不允许。

    然而，全文搜索似乎不太可能是您所需要的。它不适用于前导通配符搜索或涉及非单词的一般字符串匹配。正如文档所说：

    全文查询通过根据特定语言（如英语或日语）的规则对单词和短语进行操作，对全文索引中的文本数据执行语言搜索。全文查询可以包括简单的单词和短语或单词或短语的多种形式。

    如果您需要快速的前导通配符和/或非语言搜索，并且T-SQL 内置函数在测试后表现不佳，一种替代方法是使用 n-gram，正如我在Trigram Wildcard String Search 中所述SQL 服务器。

    那篇文章包含一个完整的实现，包括简单的触发器来保持数据源（例如上面的索引视图）在需要时始终与 n-gram 同步。如果您不需要实时同步，您可能会发现每隔一段时间完全重建 n-gram 对您的用户来说已经足够好，以至于不需要触发器（尽管它们很轻量级）。

    • 4

相关问题