Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 78 of 212

66 C H A P T E R 5 | Broader data access Default history table As an alternative, you can create a temporal table with a default history table by providing the schema and table name for the history table, as shown in Example 5-2. In this case, you must add the HISTORY_TABLE parameter to the WITH SYSTEM_VERSIONING = ON clause and include the schema explicitly with the history table's name. Optionally, you can use the HIDDEN clause when defining the period columns to exclude these columns by default in a SELECT * FROM

query. If the schema is missing or invalid or if the table already exists, the CREATE TABLE statement fails. Example 5-2: Creating a temporal table with a default history table CREATE TABLE [Production].[ProductInventory_Temporal]( [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_ProductID_LocationID] PRIMARY KEY CLUSTERED ( [ProductID] ASC, [LocationID] ASC ), [ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, [ValidTo] datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Production].[ProductInventory_Temporal_History])); Existing history table When you prefer to manage the storage or indexes of the history table, you can create it yourself and then associate it with a new temporal table, as shown in Example 5-3. The history table must be consistent with the temporal table's schema by having the same column names, the same number of columns, and the same data types for each column. In addition, the columns must be in the same order in both tables. Furthermore, the history table is subject to a few limitations. It must be in the same database as the current table, and it cannot have a primary key, foreign key constraints, unique indexes, table or column constraints, or triggers. Also, you cannot use a history table to capture changes to data or for transactional or merge replication. Note If the primary use case for a temporal table is auditing, create the history table with a rowstore table with a clustered index. If the temporal table must support analytical queries that aggregate data or apply windowing functions, add a clustered columnstore index. Example 5-3: Creating a temporal table with an existing history table -- Create history table CREATE TABLE [Production].[ProductInventory_TemporalHistory]( [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, [ValidFrom] datetime2(7) NOT NULL,

Articles in this issue

Archives of this issue

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