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 an independent consultant, trainer, and developer focusing on database & business intelligence applications. His specialties are advanced topics like data modeling, data mining, and data quality. On these toughest topics, he works and researches together with SolidQ and The Data Quality Institute. 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, with more to come. Dejan Sarka also developed and is developing many courses and seminars for SolidQ, Microsoft and Pluralsight. He is a regular speaker at many conferences worldwide for more than 15 years, including conferences like Microsoft TechEd, PASS Summit and others.

Latest posts by Dejan Sarka (see all)

» LEARN about our Master Class in BI & Analytics with Microsoft Technologies

x