SQL Server – Using The Calendar Table
Calendar & Audit tables
When I first heard about calendar tables I was intrigued. I built a calendar table to see if I could understand the hype about them but I could not see how I would ever use one until now.
Calendar table
I used Sean Smith’sDate Calendar script to build the calendar table.
The table looked interesting with useful information, but I couldn’t see how to apply it to the queries that I normally build until I had an issue with a supplier who said we had gone over our limit of user accounts.

Calendar table
I need to create a script to analyse the audit table over a time period evaluating the number of active users at each point in time. Initially I did not think about the calendar table and manually created a few iterations of the SQL with different dates, but I was finding it too restrictive until eventually I remembered about the ‘Calendar table’.
Step one – collecting on the need data to a smaller table
The audit table was large (over 50 million rows) and covered all objects in the system which had been altered. Initially I started to write queries on the audit table, but the queries could table over 40 minutes, so I had to reduce the time of the iterations from alter, run and check results.
As I only needed the data over the last year and only on user accounts which had been active. I created a smaller table (around 1600 rows) with the select data from the audit table as would allow me to run queries faster and develop the SQL faster (this would have been the ideal situation to create a heap).
The audit contained a line for every time a user accessed the system with a status of ‘login’ and a time. Working on the assumption if an account is active they are logging in before a point in time and after, both these conditions need to hold true for the account to be active. If the account hasn’t logged in at the defined point in time or hasn’t logged in after the point in time, then the account is not active.
SELECT count(*) FROM DBATools.dbo.user_audit where login_min_date <= calendar_date AND login_max_date>= calendar_date AND calendar_date = ‘2021-06-01’;
Step Two – developing the queries and using the calendar table
The queries on the smaller table were considerably faster which did enable quicker iterations of the code. I was able to quickly iterate over the my SQL code, I did not even create an index as my scratch table was so small.
Integrating the calendar table was simple as I could only see one option to implement it. For each point in time, I would have to run the count so it had to be a correlated subquery.
Using this gave me great flexibility on which dates I could select, I would start off with a single date, then increase to daily or monthly. I set the calendar year to be 2021 and I initially used one date then I used five different dates. The output I finally settled on was the first day of each week of the year.
SELECT calendar_date, ( SELECT count(*) /* like the user table to the group table */ FROM DBATools.dbo.user_audit WHERE /* logged in before and after the date */ login_min_date <= calendar_date AND login_max_date>= calendar_date ) active_users from dbatools.dbo.date_calendar DC WHERE DC.calendar_year=2021 AND dc.day_of_week = 1;
Query output

Statistics
The the query running in about 22 ms I didn’t see the point in tuning the indexes for this one off process.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
(52 rows affected)
Table ‘Worktable’. Scan count 52, logical reads 3683, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘user_audit’. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 34, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘date_calendar’. Scan count 2, logical reads 7, physical reads 3, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 21 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Conclusion
You need to use the right tool for the job so a calendar table seems essential if you need to analyse audit or log tables as it gives easy control over the granularity of the results.
I think the calendar table is a great tool to see trending of your data which might not be obvious when you first look at it.