In software performance optimization, real data sources are dynamic since business decisions often rely on the insights that can be retrieved at a moment’s notice. SQL Server 2016 has given database administrators the ability to utilize temporal tables to bring database insights to decision-makers in both real-time and from the past. These temporal tables, also known as system-versioned or assertion tables, offer the ability to query its data to be tracked as it changes over time to understand changing business trends, improve the process of auditing data as it changes, maintain slowly changing dimensions (SCD), and ease the process of recovering tables from data corruption or loss.
Temporal data tables utilize the SELECT FROM clauses with the new FOR SYSTEM_TIME expression specific for temporal table queries, along with the four sub-class expressions below:
| AS OF <date_time> | AS OF queries the column data values of the temporal table along with the SystemStartTime and SystemEndTime of its corresponding history table to return a table of data with dates that were valid at the time detailed in the expression. |
| FROM <start_date_time> TO <end_date_time> | FROM TO queries the column data values of the temporal table along with the SystemStartTime and SystemEndTime of its corresponding history table to return record data versions that are valid between the FROM datetime and the TO datetime. |
| BETWEEN <start_date_time> AND <end_date_time> | BETWEEN AND queries the column data values of the temporal table along with the SystemStartTime and SystemEndTime of its corresponding history table to return a table of data that was created or otherwise valid both on and between the BETWEEN start datetime and the AND end datetime. |
| CONTAINED IN (<start_date_time>, <end_date_time>) | CONTAINED IN queries the column data values of the temporal table and its corresponding history table to return the data of all record versions that were opened and closed within the SystemStartTime and SystemEndTime per the start datetime and end datetime in the expression. |
These temporal data query expressions come together to lower processing speed, improve productivity and decision-making, and simplify data aggregation for auditing and reporting purposes. Although SQL Server 2016 has restricting parameters on altering tables and record data to ensure security and integrity for auditing purposes, database administrators with proper permissions are able to rebuild tables, create indices, and extract statistics for the both live temporal table and its history data table.
As a Microsoft partner with experience in SQL database design and maintenance services, Ayoka Systems brings expertise in database architecture that spans industries, from health care to marketing and inventory management, to meet your enterprise database management needs.
