Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 79 of 212

67 C H A P T E R 5 | Broader data access [ValidTo] datetime2(7) NOT NULL ); GO -- Add indexes to history table CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory ON [Production].[ProductInventory_TemporalHistory]; CREATE NONCLUSTERED INDEX IX_ProductInventoryHistory_ID_PERIOD_COLUMNS ON [Production].[ProductInventory_TemporalHistory] (ValidTo, ValidFrom, ProductID, LocationID); GO -- Create temporal table CREATE TABLE [Production].[ProductInventory_Temporal_WithExisting]( [ProductID] [int] NOT NULL, [LocationID] [smallint] NOT NULL, [Shelf] [nvarchar](10) NOT NULL, [Bin] [tinyint] NOT NULL, [Quantity] [smallint] NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_ProductInventory_Temporal_WE_ProductID_LocationID] PRIMARY KEY CLUSTERED ( [ProductID] ASC, [LocationID] ASC ), ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL, ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Production].[ProductInventory_TemporalHistory])); Maintaining consistency By default, the database engine performs consistency checks when you create a temporal table or when you convert an existing table to temporal. These consistency checks include a check of the schema to ensure that the current and history tables have the correct structure and a check of the data to ensure that there are no overlapping records in these two tables and that temporal rules are enforced. The schema check validates that the following conditions are true: The current and history tables have the same number of columns. The corresponding columns in each table have matching data types. The start- and end-period columns are nonnullable. A primary key constraint exists in the current table and does not exist in the history table. If the current table has hidden period columns, the history table must also hide the period columns. There is no identity column in the history table. There are no triggers in the history table. There are no foreign keys in the history table. There are no table or column constraints (aside from default column values) in the history table. The history table cannot be in a read-only filegroup.

Articles in this issue

Archives of this issue

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