Working With Temporal Data in SQL Server

Working with temporal data in SQL Server is not supported out of the box. Learn how to implement temporal support in a SQL Server database with all of the constraints needed, and how to optimize your temporal queries.
Course info
Rating
(53)
Level
Intermediate
Updated
May 28, 2014
Duration
3h 6m
Table of contents
Introduction
Temporal Databases, Problems, and Constraints
The Interval Data Type
Packing and Unpacking Intervals
Optimizing Temporal Queries Part 1
Optimizing Temporal Queries Part 2
Optimizing Temporal Queries Part 3
Description
Course info
Rating
(53)
Level
Intermediate
Updated
May 28, 2014
Duration
3h 6m
Description

Although temporal data is part of many business applications, most RDBMS', including SQL Server, do not support it out of the box. However, before solving the problem, you need to understand it. After an introduction to temporal problems and constraints, you will learn how to implement the solutions. Many solutions are much simpler with the help of a special Interval CLR data type. Additional relational operators PACK and UNPACK are handy as well. Of course, these two operators do not exist in the Transact-SQL language. You will learn how to implement them with help of other language elements. Having a SQL Server solution for a problem does not mean the job is done. Of course, the next immediate issue is performance. Temporal queries that involve intervals are typically very IO and CPU intensive. For example, a test for overlapping intervals was solved with inefficient queries for years. However, a handful of solutions with fast queries was developed recently. This course introduces five different methods to get efficient queries that search for overlapping intervals, one of the most complex temporal problems. Of course, these solutions can be implemented for other temporal problems as well. All the solutions presented should work on any edition of SQL Server from 2008 to 2014.

About the author
About the author

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.

More from the author
Data Mining Algorithms in SSAS, Excel, and R
Intermediate
2h 59m
Jul 24, 2015
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, my name is Dejan Sarka, and this is Working with Temporal Data in SQL Server course. I'm recording this course for Pluralsight. So what is this course about? Well many applications need temporal data, need data with timestamped constraints. For example, contracts might be valid only from some time point to another time point. However, there is no support out of the box for these kinds of constraints in most contemporary relational database management systems. This is valid for SQL Server as well. SQL Server has no support for temporal data out of the box. Before solving a problem, you need to understand it. Therefore, I'm going to explain the theory behind the temporal data. After you understand the theory, then you start implementing the solution. I will use only existing SQL Server infrastructures. I will use Transact-SQL, common language runtime procedures, and existing Transact-SQL operators only. I will express temporal data and temporal constraints with existing infrastructures. However, having the solution does not mean that the job is done. You might have performance issues, performance might be inappropriate, and surely for many years there was no solution for temporal query performance problems. I can say even for decades, temporal queries were always problematic. I will present many different solutions for optimizing temporal queries in this course. These solutions were developed and they've worked only lately, so you cannot find them anywhere in any book or other course yet. Therefore, you will learn a lot of new stuff in this course.

Temporal Databases, Problems, and Constraints
Hello. This is the Temporal Databases, Problems, and Constraints module, the second module of the Working with Temporal Data in SQL Server course. In this module, I'm going to talk about temporal databases, problems, and constraints. First I will make a theoretical introduction. Then I will define temporal databases. I will start describing problems and constraints with so-called semi-temporal databases and semi-temporal propositions. Then I will describe full-temporal problems and constraints. After a couple of demos, I will summarize this module.

The Interval Data Type
Hi. This is The Interval Data Type module, the third module of the Working with Temporal Data in SQL Server course. In this module, I will do a brief introduction, and then I will develop the IntervalCID data type, an Interval on Countably Infinite Discrete sets as I defined in the previous module. I will also test how the methods of this data type work, and then I will develop a couple of constraints with the knowledge built in this IntervalCID data type. After a lot of demos, I will recapitulate this module in a brief summary.

Packing and Unpacking Intervals
Hi. This is the Packing and Unpacking Intervals module, the fourth module of the Working with Temporal Data in SQL Server course. So I will start again with an introduction, and then describe temporal constraints in a revisited way. Then I will define the pack and unpack operators. Of course, you know that they don't exist in Transact-SQL, so I will express them with existing operators. After a lot of demos, I will summarize the module.

Optimizing Temporal Queries Part 1
Hi! This is Optimizing Temporal Queries Part 1 module, the fifth module of the Working With Temporal Data in SQL Server course. In this module, I will make a brief introduction and then talk about drawbacks of the classical Transact-SQL solution. Then I will start introducing solutions for the performance problems. In this module, I will introduce Relational Interval Tree. Finally, I will make a brief summary of this module. In the first four modules, you learned about temporal databases, temporal problems, and specifically about temporal constraints and how to implement them. I have shown you a lot of solutions. However, so far I didn't care about performance issues. This module is based on work done by other people. So to remind you, we have work done by Hans-Peter Kriegel, Marco Pötke, and Thomas Seidl from the Munich University, and these guys defined the Relational Interval Tree model for optimizing temporal queries. However, building this tree was too expensive based on their algorithm. So Laurent Martin found a nice mathematics for fast computation of Relational Interval Tree nodes. And, finally, Itzik Ben-Gan created the Transact-SQL solution for the Relational Interval Tree. So in this module, I'm going to introduce this Transact-SQL solution, and while introducing it, I will also explain the Relational Interval Tree. All of these performance solutions focus on the Overlaps operator. This is probably the most complex Allen's operator. And if you solve problems with this operator, you can solve problems and performance issues with other operators as well.

Optimizing Temporal Queries Part 2
Hi! This is Optimizing Temporal Queries Part 2 module, the sixth module of the Working with Temporal Data in SQL Server course. In this module, I will make again a brief introduction and then show you different solutions for optimizing temporal queries. In the previous module, I explained what the problem is with the classical Transact-SQL solution and also explained the Relational Interval Tree solution. Now I'm going to use spatial data for temporal problems, and then I will try to solve these performance issues with the XML data type. Finally, I will show you the first useful solution, the enhanced Transact-SQL solution. And then I'll finish this module with a summary. So, as I mentioned, I will start by solving temporal queries with the spatial data types, actually with the geometry data type. This part is based on the work done by my friend, Davide Mauri. Then I will start with indigenous solutions, and the first solution you will see with XML data type will be inefficient. So, finally, at the end of this module, I will introduce the first efficient indigenous solution.

Optimizing Temporal Queries Part 3
Hi! This is Optimizing Temporal Queries Part 3 module, the seventh module of the Working with Temporal Data in SQL Server course. In this module, after the quick introduction, I'm going to show you three more solutions for optimizing the performance of the temporal queries. I will start with the enhanced T-SQL solution and use my IntervalCID data type. You might already have asked yourself why didn't I use it if I developed it in the first part of this course? I also developed the unpacked form, and I will show you how you can use the unpacked form of the relation with intervals to optimize temporal queries as well. However, the unpacked form has a big drawback. It generates many roles. So my last solution will be a compact unpacked form, unpacked form that still covers all of the time points. However, it uses many fewer rows than the full unpacked form. After that I will summarize this module and the course as a whole.