Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 80 of 212

68 C H A P T E R 5 | Broader data access Change tracking or change data capture cannot be enabled for the history table. Note If the history table is not empty, the data consistency check ensures that the end-period column value is greater than or equal to the start-period column value in each row. Converting an existing table to temporal If you have an existing table that you want to start using as a temporal table, you must add period columns and optionally add the HIDDEN flag on these columns, as shown in Example 5-4. That way, the change in the table structure does not affect existing applications. When you add the period columns, you must specify a default date and time for the start and end periods. For the start period, be sure to use a default date and time that is not in the future. The end period's default must be the maximum value for the datetime2 data type. If your existing table already has period columns, include only the PERIOD FOR SYSTEM_TIME clause in the ALTER TABLE statement and reference the existing columns as parameters. Important If you are not using Enterprise Edition, be aware that the addition of a nonnullable column to a table can be an expensive operation. Example 5-4: Converting an existing table to temporal -- Create history schema CREATE SCHEMA History; GO -- Add period columns as hidden ALTER TABLE [Production].[ProductInventory] ADD ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ValidFrom DEFAULT CONVERT(datetime2 (0), '2008-03-31 00:00:00') , ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ValidTo DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'), PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); GO ALTER TABLE [Production].[ProductInventory] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [History].[ProductInventory_History])); Understanding the effect of data changes Let's consider a simple example in which insert, update, and delete operations change data in a temporal table. First, when you insert a row into a new temporal table, the database engine sets the start-period column's value to the transaction's start time in the Coordinated Universal Time (UTC) time zone and sets the end-period column's value to 9999-12-31, as shown in Figure 5-2. All rows inserted as part of the same transaction have the same start-period column value. Figure 5-2: A new row in a temporal table. Note The start- and end-period columns are managed entirely by the database engine. It blocks any attempt to insert or update a value in these columns. Furthermore, you cannot truncate the

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016