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.