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:
