In this article, we will explore the SELECT INTO TEMP TABLE statement, its syntax and usage details and also will give some simple basic examples to reinforce the learnings.
Read more »
Temporal tables
Overview and Performance Tips of Temp Tables in SQL Server
March 31, 2021In 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.
Read more »
How to drop temp tables in SQL Server
March 23, 2020Temporary 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.
Read more »
Performing a SQL Server Audit using System-Versioned Temporal Tables
May 14, 2019In 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, 2019Temporal 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, 2017The 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, 2017Today 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, 2017It 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, 2017This 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, 2016In 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, 2016SQL 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, 2015There 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 »