Because SQL Server does not have any support for temporal data out of the box, there are always problems with this kind of data. The most important problem is the performance. Traditional solutions have to deal with slow queries, which also make slow constraints implemented through triggers.

Itzik Ben-Gan wrote an excellent article on interval queries in SQL Server by using the Relational Interval Tree model. Based on the model developed by Kriegel, Pötke, and Seidl, and enhanced by Martin, Itzik fully developed a T-SQL solution. Davide Mauri proposed usage of spatial data types. I added three additional solutions for fast querying by using just an enhanced WHERE clause in the traditional T-SQL solution, by using the unpacked form of the intervals in an indexed view or an additional table, and by using the IntervalCID CLR data type. In this post, I am simply collecting all of the links you might need to check all of the text and the code and select the best solution for you. Interesting links include:

As you can see, there is no excuse for slow queries over intervals anymore. In addition, I really hope that Microsoft is considering seriously the connect item and plans to add the temporal data support in SQL Server as soon as possible.

Dejan Sarka

Dejan Sarka, MCT and SQL Server MVP, is a trainer and consultant that focuses on development of database & business intelligence applications. Besides projects, he spends about half of the time on training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or coauthor of eleven books about databases and SQL Server. Dejan Sarka also developed many courses and seminars for SolidQ.