0秒实现100年数据的自动生成的详细教程

做测试或者生成数据仓库中的日期维度时，我们需要生成某一时间段的连续日期。大部分时候我们会用循环插入的方式来实现，但看了《High Performance T-SQL Using Window Functions》的朋友可能会有另一种方式。

先是建立表结构，简单的两个字段，一是日期，二是周几

CREATE TABLE dbo.DimTime(CurrentDay DateTime, CurrentDayOfWeek INT) 

首先来看循环的方式：

DECLARE @INC INT  = 0 
DECLARE @DateBegin DateTime = '1980-01-01'
WHILE @DateBegin <='2080-01-01'
BEGIN 
    INSERT INTO dbo.DimTime(CurrentDay  , CurrentDayOfWeek  )
        VALUES(@DateBegin,DATEPART(dw,@DateBegin)) 
    SET @DateBegin = DATEADD(Day,1,@DateBegin)
END 

(耗时 10s, 本机配了 2G内存，i3 CPU, SSD 硬盘，windows server 2012)

接下来是《High Performance T-SQL Using Window Functions》中提到的 Tally Table 方式：

DECLARE @BEGIN DATETIME = '2010-01-01'
    ,@END DATETIME = '2017-10-30'
DECLARE @INC INT;

SELECT @INC = DATEDIFF(DAY, @BEGIN, @END);

WITH L0
AS (
    SELECT *
    FROM (
        VALUES (1)
            ,(2)
            ,(3)
        ) AS T(C)
    )
    ,L1
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L0 AS a
    CROSS JOIN L0 AS b
    )
    ,L2
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L1 AS a
    CROSS JOIN L1 AS b
    )
    ,L3
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L2 AS a
    CROSS JOIN L2 AS b
    )
    ,L4
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L3 AS a
    CROSS JOIN L3 AS b
    )
    ,L5
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L4 AS a
    CROSS JOIN L4 AS b
    )

INSERT INTO dbo.DimTime(CurrentDay  , CurrentDayOfWeek  )
SELECT TOP (@INC) DATEADD(DAY, RNK - 1, @BEGIN) AS CURR_DATE, DATEPART(dw, DATEADD(DAY, RNK - 1, @BEGIN)) AS CURR_DW
FROM (
    SELECT ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) AS RNK
    FROM L5
    ) M
ORDER BY RNK

(同样硬件配置下， 0s 插入100年数据）

比较两种方法，时间上 Tally Table 占了优势，将原理部分单独出来做个函数，还可以自动生成一段连续的数字或者测试数据集，用来解决自增列序号间断的排查。

下面是对 Tally Table 原理的详细描述，请移步

Tally Table 数字辅助表

所以不妨记录下来，以后碰到有机会或者场景的时候，能随手用上。不止一次呼吁广大朋友们，读书再多，也需要动动笔头（现在动动手指记录电子档即可），记录才是可靠的。