Published on

April 21, 2010

Replacing Time Tables in SQL Server

When building a cube or other reporting mechanism in SQL Server, it is often necessary to produce a definitive list of times. One common approach is to create a table to house time values, with up to 3,600 rows for a single hour if you go as low as the seconds values in your data. However, this approach has some drawbacks and can lead to performance issues and complex queries.

One of the main issues with using a time table is that each fact table needs to join to the time table, creating a surrogate identity for time itself. This can make adding records to the fact table more complicated and can result in complex and unwieldy SQL queries. Additionally, querying a time range in the table can be slow, especially if you work at the seconds resolution.

Instead of using a time table, a better approach is to utilize the built-in DATETIME column in SQL Server. By storing the time directly in the fact table, you can write cleaner and more efficient queries. For example, instead of joining to a time table, you can simply query the fact table using a range of dates:

SELECT * FROM FactTable_T WHERE FactTime BETWEEN '1 April 2010' AND '2 April 2010'

This approach is not only more maintainable but also performs better, as an index on the FactTime column allows for fast seeking and scanning of specific time ranges within the table. SQL Server can seek to a single row in the index and then scan forward until it reaches the end of the time range, executing the query quickly.

For those using SQL Server Analysis Services, creating time dimensions can be achieved using the Common Table Expression feature. By creating a helper function, you can dynamically generate a list of timestamps between two points in time. This eliminates the need for a time table and allows for more flexibility in creating time dimensions for cubes.

Here is an example of a helper function that generates a list of timestamps:

CREATE FUNCTION fn_GetTimestampRange(@FromDate DATETIME, @ToDate DATETIME, @IncludeSeconds BIT) RETURNS TABLE AS
RETURN (
    WITH DayCTE([Date]) AS (
        SELECT CONVERT(DATETIME, CONVERT(VARCHAR(255), @FromDate, 20))
        UNION ALL
        SELECT DATEADD(DAY, 1, [Date]) FROM DayCTE WHERE Date < @ToDate
    ),
    NumberCTE([Number]) AS (
        SELECT 0
        UNION ALL
        SELECT Number + 1 FROM NumberCTE WHERE Number < 59
    )
    SELECT Result.DateKey, DATEPART(YEAR,Result.DateKey) AS [Year], DATEPART(QUARTER,Result.DateKey) AS [Quarter], DATEPART(WEEK,Result.DateKey) AS [Week], DATEPART(WEEKDAY,Result.DateKey) AS [WeekDayNumber], DATENAME(WEEKDAY,Result.DateKey) AS [WeekDayName], DATEPART(DAYOFYEAR,Result.DateKey) AS [DayOfYear], DATEPART(MONTH,Result.DateKey) AS [Month], DATEPART(DAY,Result.DateKey) AS [Day], DATEPART(HOUR,Result.DateKey) AS [Hour], DATEPART(MINUTE,Result.DateKey) AS [Minute], DATEPART(SECOND,Result.DateKey) AS [Second]
    FROM (
        SELECT DATEADD(SECOND, COALESCE(SecondCTE.Number, 0), DATEADD(MINUTE, MinuteCTE.Number, DATEADD(HOUR, HourCTE.Number, DayCTE.Date))) AS DateKey
        FROM DayCTE
        LEFT JOIN NumberCTE HourCTE ON HourCTE.Number BETWEEN 0 AND 23 AND DayCTE.Date IS NOT NULL
        LEFT JOIN NumberCTE MinuteCTE ON MinuteCTE.Number BETWEEN 0 AND 59 AND HourCTE.Number IS NOT NULL
        LEFT JOIN NumberCTE SecondCTE ON SecondCTE.Number BETWEEN 0 AND 59 AND @IncludeSeconds = 1 AND MinuteCTE.Number IS NOT NULL
        WHERE DATEADD(SECOND, COALESCE(SecondCTE.Number, 0), DATEADD(MINUTE, MinuteCTE.Number, DATEADD(HOUR, HourCTE.Number, DayCTE.Date)))) RESULT;

With this function, you can dynamically generate a list of timestamps between two points in time. For example:

SELECT * FROM dbo.fn_GetTimestampRange('1 January 2010', '1 April 2010', 1);

By replacing time tables with this dynamic approach, you can simplify your SQL queries, improve performance, and create more flexible time dimensions for SQL Server Analysis Services cubes. It’s important to note that this approach may require some adjustments based on your specific needs, such as changing the week/weekday numbering or limiting the granularity to hours instead of minutes or seconds.

Overall, by embracing this alternative approach, you can eliminate the sins of old and optimize your SQL Server solutions.

Share and enjoy! If you make use of this technique, I’d love to hear about your experience.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.