时间戳

首页 > 解决方案 > SQL Server：根据另一个表的更改自动将更新流式传输到汇总表的最佳方式

sql-server - SQL Server：根据另一个表的更改自动将更新流式传输到汇总表的最佳方式

问题描述

我目前正在一个 SQL Server 数据库中工作，其中我有一个User具有如下模式的表：

用户名 类别
用户1 赌博
用户2 赌博
用户3 运动的

我的汇总表是一个关于每个类别有多少用户UserCategoryCount的简单声明，如下所示：groupby

类别 用户数
赌博 2
运动的 1

新条目不断上传到User，我希望能够将User表中的更新流式传输到UserCategoryCount汇总表。我知道我可以创建一个groupby对User表执行 a 的简单 VIEW 语句，但我想UserCategoryCount成为它自己的表，它会根据上传到表的新用户自动更改User。

我的第一个想法是创建一个触发器来检测User表何时更新。到目前为止，我能想到的最简单但最俗气的解决方案是创建一个简单地删除和刷新的触发器UserCategoryCount：

CREATE TRIGGER TRG_Add_User
ON User
AS
BEGIN
DELETE FROM UserCategoryCount
INSERT INTO UserCategoryCount (category, numUsers)
SELECT Category, Count(Category) as numUsers
FROM User GROUP BY Category
END
GO

但这似乎是一种更新UserCategoryCount表格的非常老套的方式。任何有关如何改进此更新语句的帮助，以便我不必在每次插入新用户或一批用户时完全覆盖表，将不胜感激。

标签： sql-server

解决方案

首先，您的触发器存在严重缺陷：它不使用insertedordeleted表，而是每次都重新计算整个事物，这对性能非常不利。它也没有指定它是用于插入、更新还是删除。

一个更好的解决方案是使用索引视图。这就像一个常规视图，除了服务器在磁盘上维护实际数据，并在底层表发生更改时实时更新它。

CREATE OR ALTER VIEW dbo.UserCategoryCount
WITH SCHEMABINDING
AS
SELECT
    u.Category,
    COUNT_BIG(*) AS numUsers
FROM dbo.User u
GROUP BY u.Category;

GO

CREATE UNIQUE CLUSTERED INDEX CX_UserCategoryCount ON dbo.UserCategoryCount (Category);

索引视图有一些限制，其中：

  • 它们必须是模式绑定的，因此不能更改基础列
  • 所有表必须是两部分的，模式和表
  • 仅允许的连接是INNERor CROSS、noLEFT/RIGHT/FULL/APPLY或派生表、CTE 或子查询。
  • 如果有 a GROUP BY，则必须添加COUNT_BIG，并且唯一允许的其他聚合是SUM

推荐阅读