Introduction

Those of you that have worked extensively with dates in SQL Server (or any other relational database management system (RDBMS)) will know how finicky and complicated it can be to use DATE functions, DATEPART, DATENAME, DATEADD, GETDATE(), CURRENT_TIMESTAMP etc. Personally, I find it very useful to have a calendar table that stocks all of the necessary, pre-calculated fields in one place. Whether you’re doing BI or web, it can be very helpful to have a fixed ID for a date in order to really optimize your data analysis and processing. There are two points that you need to grasp in order to understand the following script:

1. Computed column values
2. Recursive CTE (Common Table Expressions) queries.

Computed columns

In SQL Server it is possible to create columns in a table that are merely calculations based on another column. These are called computed columns and they can come in very handy. Take this query for example: Here we are creating e a table with an auto-incremented ID that starts at 1 and another ID that effectively starts at 0 by subtracting 1 from the value of ID. It is worth noting also that these columns do not occupy disk space as they are recalculated with every query execution. However, if need be, you can tell the database engine to stock the data by adding the word PERSISTED to each column in the CREATE script. You can see an example of this in the code used below.

Recursive queries in SQL Server

This one is a bit more difficult to get your head around and don’t worry if you can’t understand it completely, the importance is that you understand the utility of it. I will try to explain it in a simple way. Basically, we create a temporary table and execute a select on a value in that temporary table. This creates a loop that we take advantage of. But don’t worry, SQL Server has a recursive limit of 100 by default. This means you won’t knock out the instance by executing an infinite loop unless you really want to. It is often useful to use recursive CTE to calculate hierarchies in a single query. You can check out more details on CTEs and recursive queries here. Once you have come to grips with these two concepts you will have the knowledge necessary to dynamically build your own calendar table. I have provided a basic example below which will create a date table with dates, years, semesters, trimesters, months, weeks and days. If you decide to implement such a table you can optimize storage by simply referencing a date ID in any table instead of stocking a full date. It will also allow you to select parts of dates instead of calculating them on the fly if you join to the calendar table.

Table and data creation

In this DDL script you see the creation of one ID column, one date column and the rest being calculations based on the date “DATE” using computed column syntax. The computed columns are then stored on the disk using the PERSISTED key word. You can go ahead and use the column examples to make whatever other columns you need. For example it may be interesting to stock actual names of days like “Monday”, “Tuesday” etc. This can be done with the DATENAME(DAY, [DATE]) function. Now you are ready to fill the calendar with dates. To do this we execute our recursive CTE query to fill only the FULLDATE column. The rest will calculate itself thanks to computed columns and, thanks to the PERSISTED clause, will be stocked on the disk and no longer need to be calculated at every execution: This script inserts every day since the 1st of January 2010 until the last day of 2015. If you need more dates you can, of course, go ahead and select a bigger date span if need be. Note that we deactivate the maximum recursion of 100 but this is a conscious decision and will not cause infinite recursion because we give the query limits in terms of the max date (WHERE DateValue + 1 < @ENDDATE) So there you have it. A lovely calendar table to use in JOINs to make your life a lot simpler when you need to search by semester or trimester. The easiest thing to do would be to create a foreign key column in your other tables that relate to the ID of the CALENDAR table. Date tables of this type are a must in SQL Server Business Intelligence they are usually called “Date Dimensions”. Luckily for those of us that don’t know how to fabricate our own ones they are mostly auto-generated. But sometimes it is useful to create a customized version as you can choose whatever calculations you want on the computed columns. Bear in mind also that computed columns and recursive CTE queries are not limited to creating calendar tables. Here is an extract of the results you will get:

See more

For BI documentation, consider ApexSQL Doc, a tool that documents SQL Server instances, databases, objects, SSIS packages, SSAS cubes, SSRS reports, Tableau server sites and SharePoint Server farms.

Useful Resources

WITH common_table_expression (Transact-SQL) Data definition language Specify Computed Columns in a Table