Alex Volok

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. That is what the documentation says and that is what we daily use in our scripts, jobs and SSIS packages. Starting with SQL Server 2016 such operation can be done also on a partition level giving extra flexibility and convenience.

Directly to an example:

Partition truncation in SQL Server 2016 as simple as following line of code:

 --SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 324 ms.
TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (1, 6 TO 10));

The command has removed partitions: 1, 6, 7, 8, 9, 10. Each one held one million rows, therefore, 6 000 000 rows removed in a 324 ms. This is amazing result which outperform PARTITION SWITCHING timings.

Behind of the hood, TRUNCATE is a DDL command and this fact is a reason of superior performance of rows/pages release. However, it also leads to requirement of the minimum permission level on an object – ALTER TABLE.

Another necessary part of the command is the list of partition ids to remove corresponding segment. Partition Id can be obtained multiple ways. As example, via $PARTITION.partition function call. Following query has been executed before and after truncation:

SELECT 
	$PARTITION.[PartitioningByRowID] (RowId) AS PartitionID
,	COUNT(*) AS [RowCount]
,	MIN(RowId) AS MinRowID
,	MAX(RowId) AS MaxRowID
FROM dbo.PartitionedTable
GROUP BY $PARTITION.[PartitioningByRowID] (RowId)

Alternative way to get partition ids and metadata is by querying querying dynamic management object – sys.dm_db_partition_stats.

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.

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 variables. But, 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.