月份: 2025 年 4 月

📅 Why You Should Create a Permanent Calendar Table in SQL Server

Creating a permanent calendar table (also known as a date dimension) is a best practice for production systems that involve reporting, scheduling, or time-based analytics. It simplifies logic, improves performance, and makes queries more readable.

🧱 What Is a Calendar Table?

A calendar table is a user-defined table with one row per date and columns for various date-related attributes.

Example Schema

CREATE TABLE dbo.Calendar (
    CalendarDate     DATE PRIMARY KEY,
    Year             INT,
    Month            INT,
    Day              INT,
    Weekday          INT, -- 1=Sunday, 7=Saturday
    DayOfWeekName    VARCHAR(10),
    IsWeekend        BIT,
    IsHoliday        BIT,
    YearMonth        CHAR(6), -- e.g. '202504'
    Quarter          INT,
    DayOfYear        INT,
    WeekOfYear       INT,
    FiscalYear       INT,
    FiscalPeriod     INT
    -- Add more fields as needed
);

✅ Benefits

FeatureBenefit
🧠 ReadabilityEasier to understand and maintain date-related logic
🕵️‍♂️ SimplicityAvoids complex DATEPART or CASE expressions
🚀 PerformancePrecomputed attributes save CPU and memory during query time
📅 FlexibilityAdd columns like fiscal period, holiday flag, ISO week, etc.
📊 BI IntegrationWorks seamlessly with Power BI, SSAS, Tableau, and other tools

🧠 Example Use Case

SELECT 
    c.YearMonth,
    COUNT(*) AS WorkdayCount
FROM dbo.Calendar c
WHERE c.CalendarDate BETWEEN '2024-07-01' AND '2025-06-30'
  AND c.IsWeekend = 0
  AND c.IsHoliday = 0
GROUP BY c.YearMonth
ORDER BY c.YearMonth;

🔧 How to Populate It

Here’s how to generate dates and populate the calendar:

DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate   DATE = '2030-12-31';

WITH DateList AS (
    SELECT @StartDate AS CalendarDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalendarDate)
    FROM DateList
    WHERE CalendarDate < @EndDate
)
INSERT INTO dbo.Calendar (
    CalendarDate, Year, Month, Day, Weekday, DayOfWeekName,
    IsWeekend, YearMonth, Quarter, DayOfYear, WeekOfYear
)
SELECT 
    CalendarDate,
    YEAR(CalendarDate),
    MONTH(CalendarDate),
    DAY(CalendarDate),
    DATEPART(WEEKDAY, CalendarDate),
    DATENAME(WEEKDAY, CalendarDate),
    CASE WHEN DATENAME(WEEKDAY, CalendarDate) IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END,
    FORMAT(CalendarDate, 'yyyyMM'),
    DATEPART(QUARTER, CalendarDate),
    DATEPART(DAYOFYEAR, CalendarDate),
    DATEPART(WEEK, CalendarDate)
FROM DateList
OPTION (MAXRECURSION 0);

🧩 Add Holiday Info

Assuming you maintain a dbo.Holidays table:

UPDATE c
SET c.IsHoliday = 1
FROM dbo.Calendar c
JOIN dbo.Holidays h ON c.CalendarDate = h.HolidayDate;

🏁 Final Thoughts

Creating and using a permanent calendar table ensures:

  • Better performance
  • Reusable logic
  • Cleaner, more maintainable SQL

💡 Tip: Build it once, use it everywhere!

MS-SQL查詢時產出日曆天暫存Table的方法

🗓️ Two Approaches to Generating Calendar Dates in SQL Server

When working with time-based reporting or scheduling systems, it’s often necessary to generate a list of calendar dates. Whether you’re counting workdays, building timelines, or joining with other datasets, having a calendar table—or generating dates on the fly—is crucial. Let’s explore two popular methods of generating calendar dates in SQL Server.

✅ 1. Using a Recursive CTE (Common Table Expression)

A recursive CTE can dynamically generate a list of dates without relying on any external table:

WITH Calendar AS (
    SELECT CAST('2024-07-01' AS DATE) AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM Calendar
    WHERE DateValue < '2025-06-30'
)
SELECT * FROM Calendar
OPTION (MAXRECURSION 0);

🔍 Pros:

  • No dependency on system tables.
  • Simple and self-contained.

⚠️ Cons:

  • Slightly less performant for very large ranges.
  • MAXRECURSION default is 100; you must override it for larger date ranges.

✅ 2. Using master.dbo.spt_values (or a numbers/tally table)

This system table contains thousands of rows and can be repurposed to generate sequential numbers (and thus, dates):

SELECT DATEADD(DAY, number, '2024-07-01') AS DateValue
FROM master.dbo.spt_values
WHERE type = 'P'
  AND number BETWEEN 0 AND DATEDIFF(DAY, '2024-07-01', '2025-06-30');

🔍 Pros:

  • Faster performance for large sets.
  • No recursion required.

⚠️ Cons:

  • Relies on an undocumented system table (spt_values), which may be unsupported or unavailable in some SQL Server environments (especially Azure SQL DB).
  • Less self-explanatory for beginners.

🧠 When to Use Which?

Use CaseRecommended Method
Small date ranges (e.g. 1 year)Recursive CTE
Large date ranges (many years)Tally table or spt_values
Need portability and readabilityRecursive CTE
Need performanceTally/spt_values

🏁 Conclusion

Both approaches have their place. The recursive CTE is elegant and portable, great for small to medium needs. If you’re working with large datasets or care about performance, using spt_values (or better, your own numbers table) is a strong alternative.

-- Suggested: Create your own Numbers table for safety
CREATE TABLE dbo.Numbers (n INT PRIMARY KEY);
WITH E1(n) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(n) AS (
    SELECT 1 FROM E1 a CROSS JOIN E1 b  -- 16 rows
),
E4(n) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM E2 a CROSS JOIN E2 b
)
INSERT INTO dbo.Numbers(n)
SELECT n FROM E4 WHERE n < 10000;

👉 Tip: For production systems, consider creating a permanent Calendar table with pre-generated dates and useful attributes (week number, holiday flag, etc.) to simplify your queries.