如何保证统计表的连续性？

In ，Joe Celko 提供了 Series 表（其他地方称为 Tally 或 Numbers）的 ANSI SQL 定义。他的定义确保列中的值是唯一的、正数且从 1 到最大值连续：

CREATE TABLE Series (
  seq INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT non_negative_nbr CHECK (seq > 0),
  CONSTRAINT numbers_are_complete CHECK ((SELECT COUNT(*) FROM Series) = (SELECT MAX(seq) FROM Series))
);

PRIMARY KEY 声明确保唯一性。积极性由约束保证non_negative_nbr。有了这两个约束，约束就保证了连续性numbers_are_complete.

SQL Server 不支持检查约束中的子查询。当我尝试创建系列表时，我收到如下错误：

Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

如果我删除不支持的约束numbers_are_complete，我留下这个定义：

CREATE TABLE Series (
  seq INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT non_negative_nbr CHECK (seq > 0)
);

当我尝试创建此版本的系列时，它成功了：

Command(s) completed successfully.

此版本的 Series 较弱，因为它不强制表中数字的连续性。

为了演示这一点，首先我必须填充表。我采用了 Itzik Ben-Gan 在他的文章中描述的技术“虚拟辅助数字表 http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers' 对 65,536 行有效地执行此操作：

WITH
N0(_) AS (SELECT NULL UNION ALL SELECT NULL),
N1(_) AS (SELECT NULL FROM N0 AS L CROSS JOIN N0 AS R),
N2(_) AS (SELECT NULL FROM N1 AS L CROSS JOIN N1 AS R),
N3(_) AS (SELECT NULL FROM N2 AS L CROSS JOIN N2 AS R),
N4(_) AS (SELECT NULL FROM N3 AS L CROSS JOIN N3 AS R)
INSERT INTO Series (
  seq
)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM N4;

该查询产生如下输出：

(65536 row(s) affected)

现在我可以从这样的表中进行选择以生成 65,536 行：

SELECT seq
FROM Series;

我已经截断了结果集，但它看起来像这样：

seq
1
2
...
65535
65536

自己检查一下，你会发现区间 [1, 65536] 中的每个数字都在结果集中。该系列是连续的。

但我可以通过删除不是范围端点的任何行来打破连续性：

DELETE FROM Series
WHERE seq = 25788;

如果强制执行连续性，此语句将引发错误，但它会成功：

(1 row(s) affected)

人类很难通过目视检查找到缺失的值。在遇到麻烦之前，他们必须首先怀疑某个值丢失了。由于这些原因，篡改 Series 数据是一种向依赖 Series 表连续的 SQL Server 应用程序引入细微错误的简单方法。

假设用户编写了一个从 Sequence 读取的查询，以枚举来自另一个源的行。经过我的篡改，该查询现在会在某个值附近产生不正确的结果 - 到第 25,788 行时，所有内容都减少了 1。

可以编写一个查询来检测 Series 表中的缺失值，但如何限制该表以使缺失值成为不可能？

我有三个可能的建议：

(1) 将数字表设置为只读（例如拒绝更新/插入/删除）。为什么你要从这个表中删除呢？您的应用程序当然不应该这样做，并且您的用户也不应该能够手动这样做。用户按下“这个按钮有什么作用？”时不需要所有这些检查约束。按钮，此时您可以简单地删除该按钮。

DENY DELETE ON dbo.Serial TO [your_app_user];
-- repeat for individual users/roles

(2) 更简单的是创建一个替代触发器来首先防止删除：

CREATE TRIGGER dbo.LeaveMyNumbersAlone
ON dbo.Serial
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;
  RAISERROR('Please leave my numbers table alone.', 11, 1);
END

是的，这可以被击败，但必须有人真正不遗余力地去做。如果您雇用的人员可能会这样做，并信任他们对数据库具有通用访问权限，请祈祷这是他们计划造成的最大损害。

是的，如果您删除/重新创建数字表或在其他地方实现它，您可能会忘记重新实现触发器。但您也可能会忘记您可能手动执行的任何处理间隙的操作。

(3) 如果您愿意即时导出数字，则可以完全避免使用数字表。为此，我使用 sys.all_columns 和 sys.all_objects 等目录视图，具体取决于我需要多少个数字：

;WITH n AS (SELECT TOP (10000) n FROM 
  (SELECT n = ROW_NUMBER() OVER
    (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
  ) AS x ORDER BY n
)
SELECT n FROM n ORDER BY n; -- look ma, no gaps!

如果只需要100行，则可以只使用其中一个视图，无需交叉连接；如果您需要更多，您可以添加更多视图。并不是试图让你远离数字表，但这会让你绕过一些限制，例如（a）在每个实例上构建一个数字表以及（b）在哲学上反对这种事情的人（我在我的研究中遇到过很多人）职业）。

顺便说一句，这确实应该出现在产品中。请投票并在以下 Connect 项目中陈述真实的业务用例：

http://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers http://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers