为什么数字表“无价”？

USE Model
GO

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

#1 楼

当您需要投影“缺失数据”时，我已经看到了许多用途。例如。您有一个时间序列（例如访问日志），并且想要显示过去30天每天的点击数（请考虑分析仪表板）。如果您执行select count(...) from ... group by day，则将获得每天的计数，但实际上您至少有一次访问的每一天的结果将只有一行。另一方面，如果您首先从数字表（select dateadd(day, -number, today) as day from numbers）中投影出一个天数表，然后将其与计数（或外层套用，不管您喜欢的是）连接在一起，那么结果将是该天的计数为0您无权访问。这只是一个例子。当然，有人可能会争辩说，仪表板的表示层可以处理丢失的日期，而只显示0，但是某些工具（例如SSRS）将无法处理此问题。我见过的其他示例使用类似的时间序列技巧（日期/时间+/-数字）来进行各种窗口计算。通常，每当使用命令式语言时，您都应使用具有已知迭代次数的for循环，SQL的声明性和集合性质可以使用基于数字表的技巧。

BTW，我感到有必要指出这样一个事实，即使使用数字表感觉就像是命令执行程序一样，也不要陷入假设它是命令性的谬论。让我举一个例子：

int x;
for (int i=0;i<1000000;++i)
  x = i;
printf("%d",x);

该程序将输出999999，这几乎可以保证。

让我们在SQL Server中使用数字表尝试相同的操作。首先创建一个包含1,000,000个数字的表：

create table numbers (number int not null primary key);
go

declare @i int = 0
    , @j int = 0;

set nocount on;
begin transaction
while @i < 1000
begin
    set @j = 0;
    while @j < 1000
    begin
        insert into numbers (number) 
            values (@j*1000+@i);
        set @j += 1;
    end
    commit;
    raiserror (N'Inserted %d*1000', 0, 0, @i)
    begin transaction;
    set @i += 1;
end
commit
go

现在让我们进行“ for循环”： >结果为：

declare @x int;
select @x = number 
from numbers with(nolock);
select @x as [@x];

如果您现在有一个WTF时刻（毕竟number是集群的主键！），这个窍门叫做分配顺序扫描，而我不是偶然插入@j*1000+@i的...您也可以冒险猜测并说出结果是因为并行性，有时这可能是正确的答案。

在这个网桥下有很多巨魔，我在On SQL Server中提到了布尔运算符短路和T-SQL函数并不暗示一定执行顺序

#2 楼

我发现数字表在各种情况下都非常有用。

在2004年写的《为什么要考虑使用辅助数字表？》中，我举了几个例子：


解析字符串
查找标识差距
生成日期范围（例如，填充日历表，这也可能是无价的）
生成时间片
生成IP范围

坏习惯：使用循环来填充大表，我展示了如何使用数字表来简化插入很多行的工作（与之相反的做法）使用while循环）。

在处理整数列表时：我的方法，以及在拆分列表时的更多内容：自定义定界符，防止重复和维持顺序，我展示了如何使用数字表拆分一个字符串（例如，一组用逗号分隔的值），并提供此方法与其他方法之间的性能比较。有关拆分和其他字符串处理的更多信息：


正确的方式拆分字符串–或次佳的选择方式
拆分字符串：后续步骤
比较字符串拆分/串联方法
从SQL Server中的字符串中删除重复项
性能意外和假设：STRING_SPLIT（）

在“ SQL Server数字表”（第1部分）中，我给出了一些有关此概念的背景知识，并会在以后的文章中详细介绍特定的应用程序。

还有许多其他用途，这些用途对我来说足够突出，足以撰写关于它们的内容。

和@gbn一样，我在堆栈溢出和此站点上也使用数字表也有一些答案。

最后，我有一系列关于生成集而没有循环，部分显示了与大多数其他方法相比使用数字表的性能优势（除了Remus的古怪离群值）：


生成无循环集-1
生成无循环集-2
生成无循环集-3

#3 楼

这是我最近从Adam Machanic那里使用的一个很好的例子：

CREATE FUNCTION dbo.GetSubstringCount
(
    @InputString TEXT, 
    @SubString VARCHAR(200),
    @NoisePattern VARCHAR(20)
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN 
    (
        SELECT COUNT(*)
        FROM dbo.Numbers N
        WHERE
            SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString
            AND PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number + LEN(@SubString), 1)) = 0
            AND 0 = 
                CASE 
                    WHEN @NoisePattern = '' THEN 0
                    ELSE PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number - 1, 1))
                END
    )
END

我使用了与CTE类似的东西来查找子字符串的特定实例（即“查找第三个此字符串中的“管道”）以使用相关的定界数据： 。基本上，数字表使您可以进行基于集合的迭代，而无需使用游标或循环。

#4 楼

每当需要SQL等效的Enumerable.Range时，我都会使用数字表。例如，我只是在此站点的答案中使用了它：计算排列数