Efficient Interval Management in SQL Server

Presented by: Itzik Ben-Gan

Running Time: 63 min

Connections Track: SQL Server

An interval represents a set of values between some low and some high values. In reality, there are different kinds of intervals that you might need to represent in your database, such as temporal (e.g., sessions, appointments, periods of validity). To represent temporal intervals in SQL Server, most people use two attributes holding the lower and upper points in time. You also might need to detect relations between intervals, such as overlap (e.g., "return all contracts that were active during an input period represented by the inputs @lower and @upper"). Some of the classic querying methods that are used to provide answers for such requests suffer from fundamental optimization problems. This session explains the existing optimization problems, presents solutions for those problems that can be applied in SQL Server, and describes the potential for additions to SQL Server that could improve interval treatment in the future.