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:
- Inside Microsoft® SQL Server® 2008: T-SQL Programming book by Itzik Ben-Gan, Dejan Sarka, Roger Wolter, Greg Low, Ed Katibah, and Isaac Kunen, Microsoft Press, 2009, especially my chapter 12, Temporal Support in the Relational Model. You can find all of the theory explained in this chapter, and the definition of the IntervalCID data type and PACK and UNPACK operators. The Date's proposed sixth normal form is discussed there as well.
- Interval Queries in SQL Server SQL Server magazine article by Itzik Ben-Gan, where he develops the Relational Interval Tree model solution in T-SQL.
- Managing Intervals Efficiently in Object-Relational Databases by Kriegel, Pötke, and Seidl of University of Munich. In this academic paper, the RI tree model is explained.
- A Static Relational Interval Tree and Advanced interval queries with the Static Relational Interval Tree by Laurent Martin. In these two papers, you can find the optimizations of the RI tree model.
- Four blog posts by me that describe the four additional solutions for optimized interval queries:
- Connect feature proposal by Itzik Ben-Gan, suggesting intervals and interval indexes integration in SQL Server. In the Workarounds section, I also added the links to these additional solutions. This does not mean that I would not want to have the temporal data integration available in SQL Server. Actually, I think this would be one of the most important features Microsoft should add in one of the next versions of SQL Server. I added these workarounds only to mitigate the problems database developers and administrators might currently have with temporal data in SQL Server.
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.