如何在SQL Server 2008中按起止日期生成日间隔并重复显示记录

Nice work getting this working already—looking for better approaches is always a smart move, especially with date expansion tasks that can get slow at scale in SQL Server 2008. Let's dive into a more efficient alternative to common recursive CTE solutions: using a tally table (a set-based approach that outperforms recursion for most cases).

Recursive CTEs work, but they process rows one at a time, which can lead to high CPU usage when dealing with large datasets or wide date ranges. Tally tables use set-based logic, which is far more efficient for generating sequences of dates.

Step 1: Generate a Tally Table (Temporary or Permanent) #

A tally table is just a list of sequential numbers. For SQL Server 2008, you can generate one on the fly using system tables, or create a permanent table if you'll use this often.

Temporary Tally Table (On-the-Fly) #

This generates a sequence of numbers from 0 to 9999 (adjust the TOP value to cover your maximum expected date interval length):

WITH Tally AS (
    SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)

Permanent Tally Table (For Repeated Use) #

If you need this functionality regularly, create a permanent table to avoid regenerating the sequence every time:

CREATE TABLE Tally (N INT PRIMARY KEY);
GO

INSERT INTO Tally (N)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;
GO

Step 2: Expand Records by Month Intervals #

Assume your source table is named YourRecords with columns ID, StartDate, EndDate, and other business columns. Use the tally table to generate each month between StartDate and EndDate:

-- Using temporary tally table
WITH Tally AS (
    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT
    r.ID,
    DATEADD(MONTH, t.N, r.StartDate) AS IntervalMonth,
    r.StartDate,
    r.EndDate,
    r.OtherColumn1, -- Replace with your actual columns
    r.OtherColumn2
FROM YourRecords r
JOIN Tally t ON DATEADD(MONTH, t.N, r.StartDate) <= r.EndDate
ORDER BY r.ID, IntervalMonth;

Step 3: Adjust for Day Intervals (If Needed) #

If you need daily intervals instead of monthly, just swap MONTH with DAY in the DATEADD function, and ensure your tally table has enough numbers to cover the longest date range:

WITH Tally AS (
    SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT
    r.ID,
    DATEADD(DAY, t.N, r.StartDate) AS IntervalDay,
    r.StartDate,
    r.EndDate,
    r.OtherColumn1,
    r.OtherColumn2
FROM YourRecords r
JOIN Tally t ON DATEADD(DAY, t.N, r.StartDate) <= r.EndDate
ORDER BY r.ID, IntervalDay;

Why This Is Better Than Recursion #

• Performance: Set-based operations like tally tables avoid the row-by-row overhead of recursive CTEs, which becomes noticeable with large datasets or date ranges spanning years.
• Stability: Recursive CTEs can hit recursion depth limits (default is 100) if your date ranges are too wide, while tally tables only require enough numbers to cover your maximum interval.

内容的提问来源于stack exchange，提问作者Eray Balkanli