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
