Working With Temporal Data in SQL Server
Pluralsight
Course Summary
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 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.
-
+
Course Syllabus
Introduction- 3m 23s
—Introduction 1m 56s
—Modules 1m 27sTemporal Databases, Problems, and Constraints- 26m 30s
—Introduction 0m 44s
—Theoretical and Practical Background 3m 38s
—Temporal Databases 2m 9s
—Demo: Mapping Dates to Integers 1m 46s
—Semi-Temporal Problems 2m 28s
—Demo: Semi-Temporal Problems 7m 43s
—Full-Temporal Problems 3m 38s
—Additional Discussions 3m 19s
—Summary 1m 1sThe Interval Data Type- 36m 25sPacking and Unpacking Intervals- 23m 18sOptimizing Temporal Queries Part 1- 30m 2sOptimizing Temporal Queries Part 2- 26m 37sOptimizing Temporal Queries Part 3- 40m 21s