adding records is referred to as _____ the database

SQL Host 2016 introduces a new type of table that is designed to keep the full story of data changes, where row validity is managed aside the system. This recently table type is called a System-Versioned Temporal Table. In earlier SQL Waiter versions, user tables would enable you to hold only the most recent copy of the row, without organism able to query the prize before the UPDATE or DELETE operations. Using a Temporal Table, you will be capable to query the recent province of the row as was common, in addition to the ability to question the full chronicle of that row, which is fully managed past the SQL Server Engine, as you stern't define the rows validity time period explicitly.

Each System-Versioned Temporal Table should check 2 datetime historic period columns that are explicitly defined in the table; the Period Start column that will be victimised by the organization to put in the start time of the row, and the Period End column in which the system will store the stop time of the row.

The main Temporal table will keep only the modern version of the table rows, simply it volition be linked automatically to another table with the same correct scheme, in which the previous versions of the modified rows will glucinium stored automatically, this table is named a History table. When you create a Lineament table, the Story table will be created automatically. If you Don River't specify the chronicle table name as a parametric quantity in the SYSTEM_VERSIONING article, it will Be named with the denotive pattern MSSQL_TemporalHistoryFor_<object_id>. Specifying the history table list while creating the Temporal table will set aside the system to create the history postpone with the default system configuration, with the ability to create your own account table and provide its appoint in the Earthly table creation statement. Take into consideration that atomic number 102 changes tin can be performed on the history table's information. If you try to delete any row from the chronicle table, you will get the (Cannot blue-pencil rows from a temporary account table '<tableName>') error, as it is only managed aside the system.

When a new row is inserted, the system will assign the Stop Starting time pillar with the current dealings starting time-founded happening the scheme clock, and assign the Period End time column with the upper limit datetime2 value equal to 9999-12-31, and the row will be opened. If an existing row is updated, the organisation will copy the previous version of the row in the history table and fill the Period End time column with the stream transaction start time supported the scheme clock, and the row will exist closed. The Period End time column value of the main temporal table volition be 9999-12-31 without some modification.

Deleting an existing row, the quarrel will be stored in the history mesa, with the Period Remainder meter tower value peer to the current transaction start sentence supported the organization clock, and the row will be winking after deleting IT from the main temporal tabular array.

System-Versioning can be enabled when a remit is created using the Make TABLE statement or after creating the table using the ALTER DATABASE statement. Ready to enable System-Versioning in a mesa, it must rich person a primary central, with two not-nullable datetime2 period columns defined Eastern Samoa GENERATED ALWAYS AS ROW START OR END, passed as parameters in the PERIOD FOR SYSTEM_TIME within the prorogue definition.

The below CREATE TABLE statement is accustomed make the Temporal_Table_Demo temporal table, with DempID primary key, the two-period of time columns; TimeStart GENERATED ALWAYS American Samoa ROW Offse and TimeEnd GENERATED ALWAYS AS ROW Closing, both selected As PERIOD FOR SYSTEM_TIME, the attribute table is linked to the dbo.Temporal_Table_Demo_History history board, attractive into consideration that distinguishing the history table scheme name is mandatory, and at long last enabling the SYSTEM_VERSIONING feature arsenic follows:

Utilise SQLShackDemo

CREATE TABLE dbo . Temporal_Table_Demo

(

[ DempID ] int NOT NULL PRIMARY KEY CLUSTERED

, [ DepName ] nvarchar ( 100 ) NOT Naught

, [ DepLocation ] varchar ( 100 ) NOT Invalid

, [ NumOfEmp ] int NOT NULL

, [ TimeStart ] datetime2 ( 2 ) GENERATED ALWAYS AS Rowing START

, [ TimeEnd ] datetime2 ( 2 ) GENERATED ALWAYS AS Course END

, PERIOD FOR SYSTEM_TIME ( TimeStart , TimeEnd )

)

WITH ( SYSTEM_VERSIONING = Happening ( HISTORY_TABLE = dbo . Temporal_Table_Demo_History ) ) ;

Browse the new temporary table from the Object Adventurer, where you will visualize a special ikon with a humble clock (see below simulacrum) for the temporal table with System-Versioned betwixt the parentheses. The history table wish not suffer a separate table node; it can be shown by expanding the temporal table with a node low the main table identified with History between parentheses. A default clustered index will be created for the history table that contains the two Period Start and Period End columns as below:

Let's assume that we pauperization to enable System-Versioning connected an existing table in our database. To do that, two Period Start and Period End non-nullable datetime2 columns should glucinium added with entirely specs specified previously as in the below script:

Interpolate TABLE dbo . AWBuildVersion ADD

[ TimeStart ] DATETIME2 ( 0 ) GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeStart DEFAULT ( '19000101' ) ,

[ TimeEnd ] DATETIME2 ( 0 ) GENERATED ALWAYS AS ROW END NOT Nix Restraint DFT_AWBuildVersion_TimeEnd Default option ( '99991231 23:59:59' ) ,

PERIOD FOR SYSTEM_TIME ( [ TimeStart ] , [ TimeEnd ] ) ;

ALTER Prorogue dbo . AWBuildVersion

DROP Restraint DFT_AWBuildVersion_TimeStart , DFT_AWBuildVersion_TimeEnd ;

ALTER Shelve dbo . AWBuildVersion SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo . AWBuildVersion _History ) ) ;

In the previous handwriting, the default constraints are definite as the period columns should live not-nullable and then dropped earlier enabling System_Versioning as the SQL Engine will be responsible filling these two columns. The SQL Server Engine will perform a consistency check on both the worldly and history tables to swan that wholly schema requirements are applied before enabling System_Versioning. This includes checking that the Menstruum End time is greater than the Geological period Start time and that the periods of the different versions of the same row will non overlap. The consistency check is enabled by default and can be disabled using the DATA_CONSISTENCY_CHECK = OFF statement in the System_Versioning enable part. Now the AWBuildVersion table is definite as a temporal table successfully:

Enabling System_Versioning on the table leave not prevent you from modifying the set back schema. The system will hold a schema shut up on both the lineament and history table, where these changes volition make up replicated to the history table. A specific schema change can't be performed directly exploitation an ALTER DATABASE statement on a table with System_Versioning enabled, such as adding or removing an Identity element, COMPUTED, SPARSE, COLUMN_SET or ROWGUIDCOL columns. To apply such changes happening a table with System_Versioning enabled, you need foremost to invalid System_Versioning, perform the change you need then enable the System_Versioning again. The below script is accustomed add a modern IDENTITY newspaper column to the Temporal_Table_Demo temporal table (we will not consider this added column in the coming demonstration):

ALTER TABLE dbo . [ dbo ] . [ Temporal_Table_Demo ] SET ( SYSTEM_VERSIONING = OFF ) ;

ALTER TABLE [ dbo ] . [ dbo ] . [ Temporal_Table_Demo ] ADD Idaho int IDENTITY ( 1 , 1 ) ;

ALTER TABLE dbo . [ dbo ] . [ Temporal_Table_Demo ] SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo . [ dbo ] . [ Temporal_Table_Demo_History ] ) ) ;

Let's bulge out a demo to investigate this new feature. First, we leave insert 5 records to the Temporal_Table_Demo temporal table using the following needle-shaped Inclose statement:

USE [ SQLShackDemo ]

INSERT INTO [ dbo ] . [ Temporal_Table_Demo ]

( [ DempID ]

, [ DepName ]

, [ DepLocation ]

, [ NumOfEmp ] )

VALUES

( 1 , 'Accounting' , 'Build1' , 10 ) ,

( 2 , 'Hour' , 'Build1' , 3 ) ,

( 3 , 'Recruitment' , 'Build1' , 2 ) ,

( 4 , 'IT' , 'Build2' , 7 ) ,

( 5 , 'Security' , 'Build2' , 3 )

Querying the temporal table to checker the inserted rows:

Superior [ DempID ]

, [ DepName ]

, [ DepLocation ]

, [ NumOfEmp ]

, [ TimeStart ]

, [ TimeEnd ]

FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

The answer in our case will be like:

As we discussed previously, the Period set out clock will be filled aside the system with the transaction start time, and the Geological period Oddment time volition be filled by the organization with the maximum datetime2 value keeping the records wide-eyed. No changes applied to the history table as atomic number 102 litigate performed on that rows yet. If you try to query IT, you will find it empty.

If we deal to delete the last row in the temporal table with DempID equal to 5:

DELETE FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ] WHERE DempID = 5

And query the temporal table, we will find that the record is removed from the postpone, which is the normal situation:

But the new affair here is that, if we query the related history table:

SELECT [ DempID ]

, [ DepName ]

, [ DepLocation ]

, [ NumOfEmp ]

, [ TimeStart ]

, [ TimeEnd ]

FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo_History ]

We will find that the deleted immortalis is inserted into the history table, and the Period End clock is updated with the transaction start time, marking the row as closed:

Once again, if we get by to update the number of employees in department 1:

UPDATE [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ] SET NumOfEmp = 11 WHERE DempID = 1

And query the temporal table once again, we bequeath find that the wrangle is updated and the Period Start time also updated with the unaccustomed transaction time, keeping the dustup staring:

And the old record before the UPDATE surgery will atomic number 4 inserted into the history postpone with the Period End time as the dealings occurrent time, closing the row:

The premature results show US how useful the new System_Versioningfeature is since it allows us to rollback or minimum get over the changes applied to that prorogue without getting back to the old relief files for one or few records.

A new clause FOR SYSTEM_TIME is added to the Prime statement to query the data across the history and main temporal tables, with five sub-clauses to provide you with many options to specify the needful time period. The early article is the AS OF article, which returns values after performing the union between the temporal and history tables and filtering the rows that are valid at the specified point. The course can atomic number 4 well-advised as validated if the Period Start time value is less than OR equal to the specified time and that the Period of time Finish clip is greater than the specified time.

The below SELECT program line uses the FOR SYSTEM_TIME AS FOR clause to retrieve all rows with TimeStart little than or equal to the provided time and the TimeEnd is greater than that provided time:

SELECT * FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

FOR SYSTEM_TIME AS OF '2016-09-18 13:40:05.06'

The final result for the legal rows after the internal pairing between the temporal and history tables will be Eastern Samoa follows:

The second clause is the CONTAINED IN clause that returns all opened and closed in rows within the time range distinct by the provided two-period values. The under SELECT statement uses the FOR SYSTEM_TIME Controlled IN clause to retrieve totally rows that opened and closed in within the time range specified by the @StartTime and @EndTime values:

DECLARE @ StartTime datetime2 = '2016-09-18 13:40:05.06'

Adjudge @ EndTime datetime2 = '2016-09-18 13:46:03.28'

SELECT * FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

FOR SYSTEM_TIME CONTAINED IN ( @ StartTime , @ EndTime )

The result in our case will be:

The one-third clause is the FROM…TO clause that returns all active rows within the time rate defined away the provided two values, regardless of the active starting time. A union will be performed internally between the main attribute put over and the history board to regain the active rows within the provided straddle of time. The below SELECT statement uses the FOR SYSTEM_TIME FROM TO clause to retrieve all active rows inside the sentence range specific aside the @StartTime and @EndTime values:

DECLARE @ StartTime datetime2 = '2016-09-18 13:40:05.06'

DECLARE @ EndTime datetime2 = '2016-09-18 13:46:03.28'

Prime * FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

FOR SYSTEM_TIME FROM @ StartTime TO @ EndTime

The infra result shows all active records within the provided range of time:

The Fourth clause is the BETWEEN…AND clause that works same As the FROM…TO clause except that it returns the only the rows that became active on the lower boundary OR became inactive on the upper boundary provided in the statement. The below SELECT statement uses the FOR SYSTEM_TIME BETWEEN AND clause to retrieve the rows that became athletic connected @StartTime or the rows that became inactive on @EndTime:

DECLARE @ StartTime datetime2 = '2016-09-18 13:40:05.06'

DECLARE @ EndTime datetime2 = '2016-09-18 13:46:03.28'

SELECT * FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

FOR SYSTEM_TIME BETWEEN @ StartTime AND @ EndTime

The question output testament make up like:

The last clause is the Completely clause that performs an inner union betwixt the rows in the main temporal table and the history table without specifying any time range as follows:

SELECT * FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

FOR SYSTEM_TIME ALL

The result will personify similar to:

As any new feature introduced in SQL Server, a related DMV or property within the DMV volition be added in order to inquiry that feature's entropy. The sys.tables system of rules tables can be queried for the tables with temporal_type not adequate zero, to retrieve all tables with System_Versioning enabled with the corresponding history tables as below:

Quality Tab1 . cite as Temporal_Table_Name ,

Tab2 . name as History_Table_Name ,

Tab1 . temporal_type_desc

FROM sys . tables Tab1

LEFT Connect sys . tables Tab2 ON Tab1 . history_table_id = Tab2 . object_id

WHERE Tab1 . temporal_type <> 0

ORDER BY Temporal_Table_Name

The result of the query will show us the two tables that we enabled the System_Versioning on with its related account tables:

The late system catalog view sys.periods can live used also to list all period columns of the tables with System_Versioning enabled on IT. Full entropy can Be retrieved by connection the sys.periods with the sys.tables as in the below query:

Quality PS . advert arsenic Period_Name ,

Tbl . name as Temporal_Table_Name ,

clms1 . name as Period_Start_CName ,

clms2 . name as Period_Enf_CName

FROM sys . periods PS

INNER JOIN sys . tables Tbl ON PS . object_id = Tbl . object_id

INNER JOIN sys . columns clms1 ON Tbl . object_id = clms1 . object_id AND PS . start_column_id = clms1 . column_id

INNER JOIN sys . columns clms2 Happening Tbl . object_id = clms2 . object_id AND PS . end_column_id = clms2 . column_id

The query result will show us the two System_Versioning enabled tables with the Period Start and Period End columns names:

SQL Server allows you to summate indexes to the temporal and history tables to enhance the question' functioning. Assume that we motive to tune the performance of the on a lower floor query that is operative very frequent in our system and any enhancement in information technology will clear a difference in the overall performance:

SET STATISTICS TIME ON

Hold @ StartTime datetime2 = '2016-09-18 13:40:05.06'

DECLARE @ EndTime datetime2 = '2016-09-18 13:46:03.28'

Superior * FROM [ SQLShackDemo ] . [ dbo ] . [ Temporal_Table_Demo ]

FOR SYSTEM_TIME FROM @ StartTime TO @ EndTime

SET STATISTICS Clip OFF

We sour the STATISTICS TIME on ready to get the question execution time accurately. Rent out's run the query and check the query execution plan victimization the APEXSQL PLAN application and execution time. The execution design of the query shows United States of America that a Clustered Index number Scan is performed in the temporal table as there is no index on the TimeStart and TimeEnd columns:

And the question took 107ms to pass around completely:

Let's make over a not-clustered index on the TimeStart and TimeEnd columns:

USE [ SQLShackDemo ]

Move on

CREATE NONCLUSTERED INDEX [ IX_Temporal_Table_Demo_TStart_TEnd ] ON [ dbo ] . [ Temporal_Table_Demo ]

(

[ TimeStart ] ASC ,

[ TimeEnd ] ASC

)

And execute the query again, the murder plan generated using the APEXSQL Programme applications programme shows us forthwith that the Clustered Exponent Scan on the Profane table metamorphic to Exponent Seek:

And that the writ of execution time is decreased clearly to 37ms:

As you keister insure, the index that we created in the temporal table enhanced the query performance and decreased the slaying time considerably. Depending on the workload happening your environment, you can customise the best index that fit your queries and enhance your system performance.

Stopping point

The System-Versioned Temporal table is a new case of table that is linked with a related history table in order to keep a history for each row change performed happening that table. You pot develop your own resolution depending on the temporal table to track the information changes with the alter time and rollback any disaster changes without the need to furbish up the backup files. You can puzzle out info about the temporal tables and the period columns using the existing and the newly introduced SQL Host system objects. The good detail here is that IT is managed only by the system requiring none effort from your side to define retention periods and keep up up the insertion Beaver State deletion processes on the historical tables. Eastern Samoa any new feature article, test it with kid gloves in your test environment before applying it to the live environment.

Writer
Recent Posts

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL Bi, SQL Waiter Database Establishment and Development fields.

He is a Microsoft Qualified Solution Expert in Information Management and Analytics, Microsoft Certified Resolution Associate in SQL Database Governance and Development, Azure Developer Associate and Microsoft Certified Flight simulator.

Also, he is contributory with his SQL tips in galore blogs.

Regar all posts by Ahmad Yaseen

adding records is referred to as _____ the database

Source: https://www.sqlshack.com/track-history-data-changes-using-sql-server-2016-system-versioned-temporal-tables/