Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 76 of 212

64 C H A P T E R 5 | Broader data access for slowly changing dimensions. Or you might need to perform a trend analysis to compare values for a category at different points in time or find the value of a business metric at a specific point in time. To address these various needs for handling changes to data, SQL Server 2016 now supports temporal tables, which were introduced as a new standard in ANSI SQL 2011. In addition, Transact-SQL has been extended to support the creation of temporal tables and the querying of these tables relative to a specific point in time. A temporal table allows you to find the state of data at any point in time. When you create a temporal table, the system actually creates two tables. One table is the current table (also known as the temporal table), and the other is the history table. The history table is created as a page-compressed table by default to reduce storage utilization. As data changes in the current table, the database engine stores a copy of the data as it was prior to the change in the history table. The use of temporal tables has a few limitations. First, system versioning and the FileTable and FILESTREAM features are incompatible. Second, you cannot use CASCADE options when a temporal table is the referencing table in a foreign-key relationship. Last, you cannot use INSTEAD OF triggers on the current or history table, although you can use AFTER triggers on the current table. Creating a rowstore temporal table When you create a temporal table, you must include a primary key and nonnullable period columns, a pair of columns having a datetime2 data type that you use as the start and end periods for which a row is valid. You should avoid using blob data types—(n)varchar(max), varbinary(max), ntext, and image—in a temporal table because of their size and the adverse performance impact that results. You must also specify one of the following conditions for the history table: Anonymous The system creates the history table with an automatically generated name in the same schema as the current table. This option is good for prototyping a solution because you do not need to concern yourself with naming conventions. Default The system creates the history table by using the schema and table names that you specify. Existing You specify an existing history table with a schema that conforms to the temporal table. This table cannot have a primary key defined. Note When the system creates a history table for a partitioned current table, it creates the table on the default filegroup and does not configure partitioning for the history table. Anonymous history table To create a temporal table with an anonymous history table, you define the structure of your table, including a primary key and the period columns, as shown in Example 5-1. The period columns are always nonnullable even if you omit the NOT NULL constraint. Notice the PERIOD FOR SYSTEM_TIME argument that identifies the period columns. Last, the SYSTEM_VERSIONING = ON argument is necessary to enable the temporal feature. Example 5-1: Creating a temporal table with an anonymous history table CREATE TABLE [Production].[ProductInventory_Temporal_Auto]( [ProductID] [int] NOT NULL, [LocationID] [smallint] NOT NULL, [Shelf] [nvarchar](10) NOT NULL, [Bin] [tinyint] NOT NULL, [Quantity] [smallint] NOT NULL,

Articles in this issue

Archives of this issue

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