Performing a SQL Server Audit using System-Versioned Temporal Tables May 14, 2019 by Ahmad Yaseen In previous articles of this SQL Server Audit series (see the TOC at the bottom), we discussed the main concept and real-life needs for having a SQL Server Audit solution. After that, we went through the different methods that can be used to audit the SQL Server instances and databases. In this article, we will show how to take advantages of the System-versioned Temporal Tables feature to audit database table DML changes. Read more »
Temporal Tables in SQL Server February 28, 2019 by Prashanth Jayaram Temporal tables have been a feature of SQL Server since version 2016. SQL Server professionals have historically had several options to track data change events. The evolution of the data tracking mechanism started with Change tracking (CT), Change Data Capture (CDC) and now Temporal Tables. Read more »
SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation June 21, 2017 by Ahmad Yaseen The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically. Read more »
Temporal Table applications in SQL Data Warehouse environments March 7, 2017 by Hans Michiels Today the subject of investigation is the Temporal Table, which is a new feature in SQL Server 2016. My focus will slightly be on how to use it in Data Warehouse environments, but there is some general information passing by as I write. Read more »
When to Use SQL Temp Tables vs. Table Variables February 21, 2017 by Aamir Syed It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session. Read more »
How to query data in a System-Versioned Temporal Tables in SQL Server February 20, 2017 by Marko Zivkovic This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size. Read more »
Concept and basics of Temporal tables in SQL Server 2016 November 24, 2016 by Mustafa EL-Masry In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including: Read more »
How to track the history of data changes using SQL Server 2016 System-Versioned Temporal Tables September 23, 2016 by Ahmad Yaseen SQL Server 2016 introduces a new type of table that is designed to keep the full history of data changes, where row validity is managed by the system. This new table type is called a System-Versioned Temporal Table. In earlier SQL Server versions, user tables would enable you to hold only the most recent copy of the row, without being able to query the value before the UPDATE or DELETE operations. Using a Temporal Table, you will be able to query the recent state of the row as usual, in addition to the ability to query the full history of that row, which is fully managed by the SQL Server Engine, as you can’t define the rows validity period explicitly. Read more »
New Features in SQL Server 2016 – Temporal Data Tables July 6, 2015 by Kenneth M. Nielsen There are many new features in SQL Server 2016, but the one we will focus on in this post is: Temporal Database Tables One of the many new features in SQL server 2016 is the built-in support for temporal data tables. This is a feature that many developers have been urging to have as a “out of the box feature” – and therefore many have developed their own ways of implementing the capability to store information about how data looked in a certain period of time. Read more »
