SQL Server 2016: Temporal Tables. Changing the Schema

In the previous blog post, I made an introduction to the basic concepts behind temporal tables. This time I would like to cover, in a little bit more detailed way, one difficult topic – schema change: how it can be applied to temporal tables and what impact on the system it can bring.

Foreword

This post contains performance measurements of certain DDL commands. However, the absolute value of such metrics makes not so much sense due to the no-reproducibility of execution environment on the reader side. Therefore, I will provide command execution time not just for schema change operations, but also for data load, index creation, data manipulation commands, so all timespans can be matched with each other to make overall understanding about the impact.

Continue reading…

SQL Server 2016: Temporal Tables. Introduction

SQL Server 2016 CTP2 finally available for the public and it brings many new features, which are worth checking. However, one of those new initiatives, I believe, can bring some discussions in DWH/BI environments. The name of it is Temporal table.

Foreword

It seems, the feature can bring another naming confusion. Currently SQL Server ships  temporary tables and table variablesBut, with a new release, temporal table is a new guy in a toolbox, however, the usage of the new tool is completely different:

A temporal table is a table for which a PERIOD definition exists and which contains system columns with a datatype of datetime2 into which the period of validity is recorded by the system, and which has an associated history table into which the system records all prior versions of each record with their period of validity. With a temporal table, the value of each record at any point in time can be determined, rather than just the current value of each record. A temporal table is also referred to as a system-versioned table. Read more in online documentation.

Continue reading…