Maintenance & Metadata

My last chapter is about the maintenance backup/restore, index rebuild/reorg., etc. and the Metadata.
I divide my article into 4 parts to be clearer and easier to read:
Part I – Principle & Creation
Part II – INSERT, UPDATE & DELETE commands
Part III – SELECT command
Part IV – Maintenance & Metadata

SQL Server version/built used for this serie of articles is CTP 2.2/13.0.407.1

Backup & Restore

Before my Backup, I run a database integrity Check with DBCC CHECKDB
temporal48
The check go through the History table.
A traditional Backup from your database, stores the table and its history table.
I don’t see any option to have a backup just from the table and not include the history table.
The restore process like the backup process, restores both tables.

Index Rebuild & Reorg

The first step is to see if the clustered index from History table is in the fragmentation view.
temporal40
Cool, I see it!
To be sure that I can see all indexes that I create on the history table, I create a new Non-Clustered Index and rerun the query
temporal41
The next step is to verify if I can Rebuild or Reorganize indexes in the History table.
temporal42
It’s running perfectly good:
temporal43
I’m sorry for my sample, while I haven’t enough fragmentation to complete totally the test.

Metadata

sys.tables

temporal45
Sys.tables has 3 new columns for temporal table:

  • temporal_type with 0 for a standard table, 1 for the history table and 2 for the system-versioned table
  • temporal_type_desc with the description of the 3 types from the column temporal_type
  • history_table_id is an id for the system-versioned table.

sys.periods

temporal46
Sys.periods gives you the information about the start and end dates and times columns in your system-versioned table.
MSDN reference for sys.periods here

Property “TableTemporalType“ in OBJECTPROPERTYEX

temporal47
My last is point is the new property TableTemporalType in OBJECTPROPERTYEX who precise the type of the table like temporal_type in the sys.tables

CORE MESSAGE

  • Maintenance Plan (Backup/Restore, Index rebuild/Reorg, Integrity check) goes through the system-versioned table and history table like a standard table.
  • Sys.tables has 3 new columns for temporal table: temporal_type, temporal_type_desc and history_table_id.
  • Sys.periods is a new metatdata to see which defines the period in the system-versioned table.

MORE INFORMATION

  • Msdn for Temporal Table here
  • A 20 minutes video is available on Channel9 here

This finishes my series of articles on this very good new feature in SQL Server 2016. Enjoy to use it!