Temporal tables

Overview and Performance Tips of Temp Tables in SQL Server

March 31, 2021 by Esat Erkec

In this article, we will learn the essentials of the temp tables in SQL Server and we will also discuss some performance tips about temporary tables.

How to drop temp tables in SQL Server

March 23, 2020 by Esat Erkec

Temporary tables, also known as temp tables, are widely used by the database administrators and developers. However, it may be necessary to drop the temp table before creating it. It is a common practice to check whether the temporary table exists or not exists. So, we can eliminate the “There is already an object named ‘#temptablename’ in the database” error during the temporary table creation.

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.

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.

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.

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.

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.

$C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\14.png$

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.

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:

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.

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.