SQL Server 產生完整日期維度表：時間維度建模與 WHILE 迴圈實作

為什麼需要日期維度表？

在資料倉庫與 BI 分析中，日期維度 是核心共用維度。預先產生涵蓋數年、含豐富屬性（週、月、季、財務期、工作日旗標等）的日期表，可：

• 避免重複計算日期屬性（DATEPART、DATENAME）
• 支援「無銷售日期也要顯示 0」的外部聯接需求
• 統一全企業的週/月/季定義（如財務年度起始月）
• 加速切片切塊查詢，減少掃描事實表

資料表結構設計

CREATE TABLE [dbo].[time_dimension] (
    [time_id]       INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [the_date]      DATETIME NULL,          -- 日期值
    [the_day]       NVARCHAR(15) NULL,      -- 星期名稱
    [the_month]     NVARCHAR(15) NULL,      -- 月份名稱
    [the_year]      SMALLINT NULL,          -- 年份
    [day_of_month]  SMALLINT NULL,          -- 月份第幾天
    [week_of_year]  SMALLINT NULL,          -- 年份第幾週
    [month_of_year] SMALLINT NULL,          -- 月份編號 1-12
    [quarter]       NVARCHAR(2) NULL,       -- 季度 Q1-Q4
    [fiscal_period] NVARCHAR(20) NULL       -- 財務期間（可擴充）
);

WHILE 迴圈填充資料腳本

DECLARE 
    @WeekString     VARCHAR(12),
    @dDate          SMALLDATETIME,
    @sMonth         VARCHAR(20),
    @iYear          SMALLINT,
    @iDayOfMonth    SMALLINT,
    @iWeekOfYear    SMALLINT,
    @iMonthOfYear   SMALLINT,
    @sQuarter       VARCHAR(2),
    @adddays        INT;

SET @adddays = 1;              -- 日期遞增間隔（1=每日，7=每週）
SET @dDate = '2002-01-01';     -- 起始日期

WHILE @dDate < '2004-12-31'    -- 結束日期
BEGIN
    SELECT @WeekString   = DATENAME(dw, @dDate);      -- 星期名稱
    SELECT @sMonth       = DATENAME(mm, @dDate);      -- 月份名稱
    SELECT @iYear        = DATENAME(yy, @dDate);      -- 年份
    SELECT @iDayOfMonth  = DATENAME(dd, @dDate);      -- 日
    SELECT @iWeekOfYear  = DATENAME(week, @dDate);    -- 週號
    SELECT @iMonthOfYear = DATEPART(month, @dDate);   -- 月份編號
    SELECT @sQuarter     = 'Q' + CAST(DATENAME(quarter, @dDate) AS VARCHAR(1));

    INSERT INTO time_dimension (
        the_date, the_day, the_month, the_year,
        day_of_month, week_of_year, month_of_year, quarter
    ) VALUES (
        @dDate, @WeekString, @sMonth, @iYear,
        @iDayOfMonth, @iWeekOfYear, @iMonthOfYear, @sQuarter
    );

    SELECT @dDate = DATEADD(day, @adddays, @dDate);   -- 遞增日期
END
GO

SELECT * FROM time_dimension;

進階擴充：實用欄位一鍵新增

ALTER TABLE time_dimension ADD
    is_weekend        AS (CASE WHEN DATENAME(dw, the_date) IN ('Saturday','Sunday') THEN 1 ELSE 0 END),
    is_month_start    AS (CASE WHEN DAY(the_date) = 1 THEN 1 ELSE 0 END),
    is_month_end      AS (CASE WHEN DAY(DATEADD(day,1,the_date)) = 1 THEN 1 ELSE 0 END),
    fiscal_year       AS (CASE WHEN MONTH(the_date) >= 7 THEN YEAR(the_date)+1 ELSE YEAR(the_date) END), -- 7 月起算
    fiscal_quarter    AS ('FQ' + CAST((MONTH(the_date)+2)/3 AS VARCHAR(1))),
    yyyymmdd          AS (CONVERT(CHAR(8), the_date, 112)),  -- 20240115 格式
    yyyymm            AS (CONVERT(CHAR(6), the_date, 112));  -- 202401 格式

使用 計算欄位 零儲存成本、永遠同步、查詢可直接使用索引（若建立索引）。

效能優化建議

1. 叢集索引：CREATE CLUSTERED INDEX IX_time_dimension_date ON time_dimension(the_date);
2. 非叢集索引：常用篩選欄位（the_year, month_of_year, is_weekend）
3. 資料壓縮：DATA_COMPRESSION = PAGE 節省空間
4. 分割資料表：跨年份分割，配合分割函數/配置

現代替代：遞迴 CTE 一次產生（SQL 2005+）

WITH Dates AS (
    SELECT CAST('2002-01-01' AS DATE) AS the_date
    UNION ALL
    SELECT DATEADD(day, 1, the_date)
    FROM Dates
    WHERE the_date < '2004-12-31'
)
SELECT 
    the_date,
    DATENAME(dw, the_date) AS the_day,
    DATENAME(mm, the_date) AS the_month,
    YEAR(the_date) AS the_year,
    DAY(the_date) AS day_of_month,
    DATEPART(week, the_date) AS week_of_year,
    MONTH(the_date) AS month_of_year,
    'Q' + CAST(DATEPART(quarter, the_date) AS VARCHAR(1)) AS quarter
INTO time_dimension
FROM Dates
OPTION (MAXRECURSION 0);

CTE 寫法無需迴圈、集合導向、效能更佳，建議新專案採用。