Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 81 of 212

69 C H A P T E R 5 | Broader data access current table. If you need to clear data from the table, you must disable system versioning by using the ALTER TABLE statement with the WITH SYSTEM_VERSIONING = OFF argument. When we update the row in the temporal table, the database engine performs the update, adjusts the start-period column's value, and inserts a new row into the history table, as shown in Figure 5-3, which displays the temporal table in the top result set and the history table in the bottom result set. The history table contains the row values as they were when the row was inserted into the temporal table, except that the end-period column value is set to the transaction time of the update operation. Note that this time matches the start-period column value for the corresponding row in the temporal table. Figure 5-3: An update row in a temporal table and a corresponding row containing its original values in a history table. Next, when you delete the row in the temporal table, the database engine removes it from the table and inserts a copy of it into the history table. The copy retains all the values from the temporal table except that the end-period time is set to the transaction time of the deletion, as shown in Figure 5-4. Figure 5-4: An empty row in a temporal table and rows in a history table containing the original values of a row and its values at the time of its deletion in a history table. Note When you perform a MERGE operation, the database engine modifies the temporal and history tables based on the specific INSERT, UPDATE, or DELETE operation affecting each row. Using memory-optimized temporal tables The velocity of change to your data determines how quickly history tables can grow. This aspect of the temporal feature can pose a challenge for memory-optimized tables because data can grow faster than the available memory. Furthermore, historical data is typically read less frequently than current data. For these reasons, having a memory-optimized history table is not recommended. However, you can create a durable memory-optimized temporal table and then store its history table on disk, as shown in Example 5-5. Consider adding a clustered columnstore index on the history table and a nonclustered index on the period columns (with the end-period column first) and the column or columns in the history table that correspond to the column (or columns) defined as the primary key in the current table. Example 5-5: Creating a memory-optimized temporal table with a disk-based history table -- Create history table CREATE TABLE [Production].[ProductInventory_TemporalHistory_Disk]( [ProductID] [int] NOT NULL, [LocationID] [smallint] NOT NULL,

Articles in this issue

Archives of this issue

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