Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 84 of 212

72 C H A P T E R 5 | Broader data access BETWEEN AND Returns any version of a row that was active in the specified range of dates from either the current table or the history table. It is similar to the FROM/TO subclause, except in this case a row is considered active if its start period is earlier than or equal to the end date/time parameter value. The end period must still be later than the start date/time parameter value. CONTAINED IN (, ) Returns any version of a row that was active only during the specified range of dates from either the current table or the history table— that is, the start period is equal to or later than the start date/time parameter value and the end period is earlier than or equal to the end date/time parameter value. ALL Returns all row versions regardless of periods. Example 5-7: Querying a temporal table SELECT ProductID, LocationID, Shelf, Bin, Quantity, ModifiedDate, ValidFrom, ValidTo FROM Production.ProductInventory_Temporal_Auto FOR SYSTEM_TIME BETWEEN '2016-02-29 00:00:00.0000000' AND '2016-03-01 00:00:00.0000000' WHERE ProductID = 1 ORDER BY ValidFrom; Note Because the period columns store UTC date/time values, be sure to adjust your local time to UTC time when providing a date/time parameter value in a temporal query. Securing temporal tables Temporal tables require layers of security in addition to those for standard tables. This security applies to CREATE TABLE, ALTER TABLE, and SELECT statements to control who can perform schema operations and who can access data. To enable or disable system versioning on a temporal table, you must have the CREATE TABLE permission in the database and the ALTER permission on the schemas for the current and history tables. If you are using an existing history table when you convert a current table to temporal, you must have CONTROL permission on both the current table and the history table. After you enable system versioning for a table, you cannot drop the table or use the ALTER TABLE statement to add or drop the period columns. However, you can use the ALTER TABLE statement for partitioning, but you can only switch a partition into the current table or out of the history table while system versioning is enabled. You can also use the ALTER TABLE statement to create an index or statistics or to rebuild the table. If you grant a user SELECT permission on the current table only, the user can view data from that table but cannot access data from the history table by using a temporal query clause. If a user requires access to historical data, you must grant that user SELECT permission on both the current table and the history table. Managing data retention Data grows quickly as you store historical data, so you should consider how long you must keep the data available in the history table and how to move it out of that table when the retention period expires. To move data from the history table, you have the following options:

Articles in this issue

Archives of this issue

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