主页 / dba / 问题 / 187533
Accepted
Brent Ozar
Asked: 2017-10-04 06:47:30 +0800 CST

如何在 SQL Server 2017 中使用 SNAPSHOT_MATERIALIZATION 创建视图？

  • 772

SQL Server 2017 有几个新的存储过程：

  • sp_refresh_single_snapshot_view – @view_name nvarchar(261)、@rgCode int 的输入参数
  • sp_refresh_snapshot_views – @rgCode int 的输入参数

sys.messages 中的新条目：

  • 10149 – 无法在视图 '%.*ls' 上创建具有 SNAPSHOT_MATERIALIZATION 的索引，因为视图定义包含内存优化表。
  • 10642 – 不能为 '%.*ls' 上的索引 '%.*ls' 设置 SNAPSHOT_MATERIALIZATION，因为它仅适用于视图上的索引。
  • 10643 – 不能为 '%.*ls' 上的 '%.*ls' 设置 SNAPSHOT_MATERIALIZATION，因为它仅适用于视图上的聚集索引。
  • 10648 – 不能为 '%.*ls' 上的分区索引 '%.*ls' 设置 SNAPSHOT_MATERIALIZATION。
  • 10649 – 无法在具有 SNAPSHOT_MATERIALIZATION 的聚集索引 '%.*ls' 的 '%.*ls' 上创建非聚集索引 '%.*ls'。
  • 10650 – 刷新快照视图需要在数据库上启用快照隔离。
  • 3760 – 无法删除具有 SNAPSHOT_MATERIALIZATION 的视图 '%.*ls' 上的索引 '%.*ls'。
  • 4524 – 无法更改视图 '%.*ls'，因为它具有快照实现。
  • 4525 – 在刷新视图之前，无法对具有快照实现的视图 '%.*ls' 使用提示 '%ls'。

和新的扩展事件：

快照视图扩展事件

那么我们如何创建快照物化视图呢？（显然，微软还没有记录它。）这是我迄今为止尝试过的东西的要点。

sql-server materialized-view

1 个回答

  1. Best Answer

    你不能。该功能在 2017 RTM 中被禁用。

    也就是说，你可以...

    使用 AdventureWorks：

    CREATE VIEW dbo.TH
WITH SCHEMABINDING
AS
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
GROUP BY P.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

    对基础表的更改不会立即反映在视图中（SQL Server 通常就是这种情况）。同样，对基础表的数据修改不必维护快照索引视图。

    要刷新视图内容，需要调用新的存储过程之一：

    EXECUTE sys.sp_refresh_single_snapshot_view
    @view_name = N'dbo.TH',
    @rgCode = 0; -- don't know what this is for yet

    这会产生执行计划：

    计划

    这可能对你不起作用，因为要么需要一个未记录的跟踪标志，要么你需要做我做的特别讨厌的事情：写入保存功能标志的内存位置（使用调试器）以启用此功能。

    如果您好奇，功能标志是sqllang!g_featureSwitchesLangSvc+0x10f. 期间检查sqllang!SpRefreshSingleSnapshotView。

    如果你想一起玩，并准备好接受在 SQL Server 运行时对其代码进行黑客攻击的后果，并使用 Microsoft 认为尚未准备好的功能：

    1. 将调试器附加到 SQL Server 2017 进程。我使用WinDbg。

    2. 设置断点：

      bp sqllang!SpRefreshSingleSnapshotView

    3. g使用 Go 命令 ( )恢复 SQL Server

    4. 创建上面的视图，但还没有唯一的聚集索引
    5. 运行sys.sp_refresh_single_snapshot_view上面的命令

    6. 当断点被命中时，单步执行直到看到代码行：

      cmp byte ptr [sqllang!g_featureSwitchesLangSvc+0x10f (00007fff`328dfbcf)],0

      其他版本中的偏移量可能会有所不同，例如在 2017 RTM CU3 中sqllang!g_featureSwitchesLangSvc+0x114

    7. 括号内的内存地址可能不同。使用你看到的那个。

    8. 使用 display memory 命令查看找到的内存地址处的当前值：

      db 00007fff`328dfbcf L1

    9. 这应该显示一个零，表示该功能已禁用。

    10. 使用 enter values 命令（再次使用您的内存地址）将零更改为一：

      eb 00007fff`328dfbcf 1

    11. 禁用断点并继续运行 SQL Server。

    12. 该功能现已启用。
    13. 在视图上构建唯一聚集索引。
    14. 到处玩。

    注意SNAPSHOT_MATERIALIZATION允许我们具体化通常无法索引的查询规范的快照，例如以下使用MAX：

    CREATE VIEW dbo.TH2
WITH SCHEMABINDING
AS
SELECT TH.ProductID, MaxTransactionID = MAX(TH.TransactionID)
FROM Production.TransactionHistory AS TH
GROUP BY TH.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH2 (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

    结果：

    命令成功完成。
    • 58

相关问题