Tag Archives: Sql Server 2016

Row level security in Sql Server 2016

Row level security is one of the new feature introduced in Sql Server 2016, it provides a mechanism to control row level read and write access based on the user’s context data like identity, role/group membership, session/connection specific information (I.e. like CONTEXT_INFO(), SESSION_CONTEXT etc) etc.

This article explains this new feature with simple and extensive list of examples.

The logic to control the access to table rows resides in the database and it is transparent to the application or user who is executing the query. For example a database user executing a query SELECT * FROM Customers may feel that he has complete access to the Customers table as this query is returning the result without any exception, but with row level security in-place we can make the DataBase engine internally change the query something like for example: SELECT * FROM Customers Where AccountManager = USER_NAME().

[ALSO READ] Dynamic Data Masking in Sql Server 2016

This feature is very useful in scenarios like below:

  • Shared Hosting/Multi-tenant scenario where common database used by multiple tenants to store the data. In such cases we want each tenant is restricted to access their data only.
  • In a traditional database, where we want to control the access to rows based on user’s role.

Parts of Row-Level Security
Parts of Row Level Security
Following are the three main parts of a Row-Level Security

  1. Predicate Function
    A predicate function is an inline table valued schema bound function which determines whether a user executing the query has access to the row based on the logic defined in it.
  2. Security Predicate
    Security Predicate is the one which binds a Predicate Function to the Table.

    There are two types of security predicates

    1. Filter Predicate
      It filters-out the rows from the SELECT, UPDATE or DELETE operation to which user doesn’t have access based on the logic in the Predicate function. This filtering is done silently without notifying or raising any error.
    2. Block Predicate
      It blocks user from INSERT, UPDATE or DELETE operation by explicitly raising the error if the row doesn’t satisfy the predicate function logic.

      There are four types of BLOCK predicates AFTER INSERT, BEFORE UPDATE, AFTER UPDATE and BEFOR DELETE.

  3. Security Policy
    Security policy is a collection of a Security Predicates which are grouped in a single new object called Security Policy.

[ALSO READ] New Features in Sql Server 2016

Enough theory let us jump on to the example which explains this theory in simple understandable term:

EXAMPLE: Let us take a scenario where we have a Cricket related database with Players table as shown in the below image. Assume that the players table has all the cricket playing countries team’s player data. When individual country cricket board application/user access this table data we want to return only the players belonging to that country.

Row Level Security Example

Script to create demo CRICKET database and Players table with sample player’s data

CREATE DATABASE CRICKET
GO
USE CRICKET
GO
CREATE TABLE dbo.Players
(
	PlayerId INT IDENTITY(1,1),
	Name	NVARCHAR(100),
	Country NVARCHAR(50),
	UserName sysname
)
GO
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Sachin Tendulkar', 'India', 'BCCI_USER'),
	  ('Rahul Dravid', 'India', 'BCCI_USER'),
	  ('Anil Kumble','India', 'BCCI_USER'),
	  ('Ricky Ponting','Australia', 'CA_USER'),
	  ('Shane Warne','Australia', 'CA_USER')
GO

Execute the following statement to retrieve all the records from the Players table

SELECT * FROM dbo.Players

RESULT:
Row Level Security Example Table

Let us create three test user accounts by executing the following script

--Indian cricket board user
CREATE USER BCCI_USER WITHOUT LOGIN
-- Australian cricket board user 
CREATE USER CA_USER WITHOUT LOGIN    
--Admin user (International Cricket Council user)
CREATE USER ICC_USER WITHOUT LOGIN 

Let us execute the following script to grant read access on the Players table for the above three newly created users

GRANT SELECT ON Players TO BCCI_USER
GRANT SELECT ON Players TO CA_USER
GRANT SELECT ON Players TO ICC_USER

Let us create a Predicate function which returns 1 when user has access to the row by the following script. For this example we will use simple logic to determine the access to the row. The logic in this case is, if the name of the user who is executing the query matches with the UserName column value of that row or the name of the user who is executing the query is ICC_USER the user is allowed to access the row.

--Players predicate function
<pre class="brush: sql; gutter: false">
CREATE FUNCTION dbo.PlayersPredicateFunction
( @UserName AS SYSNAME )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN	SELECT 1 AS AccessRight
	WHERE @UserName = USER_NAME() OR USER_NAME() = 'ICC_USER'
GO

Let us create a Security Policy by executing the following script which is adding the above predicate function as a filter predicate on the Players table

--Security policy
CREATE SECURITY POLICY PlayersSecurityPolicy
--Security Predicate
ADD FILTER PREDICATE 
dbo.PlayersPredicateFunction(UserName) ON dbo.Players
WITH (STATE = ON)

Now execute the below statement to see whether I am (i.e. dbo owner) still able to fetch all the records from the Players table, which I was able to do prior to creating the security policy.

SELECT * FROM dbo.Players

RESULT
Row Level Security Post adding Security Policy

From the result it is clear that the filter predicate is filtering out all the rows as the SA user with which I am executing the query doesn’t have the access to any rows as per the predicate function definition. And below is the execution plan of this query:

Row Level Security Filter Predicate execution plan

From the execution plan we can see that the simple query SELECT * FROM dbo.Players is getting converted internally by the database engine as SELECT * FROM dbo.Players WHERE UserName = USER_NAME() OR USER_NAME() = ‘ICC_USER’ because of the Filter Predicate on the Players table

Execute the following query to see the rows to which the BCCI_USER user has access

EXECUTE AS USER = 'BCCI_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security BCCI_USER row access

From the result we can see that BCCI_USER with which the above query is executed can see only Indian cricket players as only these players UserName column value matches with the BCCI_USER.

Execute the following query to see the rows to which the CA_USER user has access

EXECUTE AS USER = 'CA_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security CA_USER row access

From the result we can see that CA_USER with which the above query is executed can see only Australian cricket players as only these players UserName column value matches with the CA_USER.

As per the filter predicate function definition the ICC_USER should be able to see all the Players rows. Let us confirm this by executing the following query.

EXECUTE AS USER = 'ICC_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security ICC_USER row access

Grant the DML operations permission on the Players table to the above three newly created users by executing the following statement

GRANT INSERT, UPDATE, DELETE ON Players TO BCCI_USER
GRANT INSERT, UPDATE, DELETE ON Players TO CA_USER
GRANT INSERT, UPDATE, DELETE ON Players TO ICC_USER

Let us execute the following statement to see whether the user BCCI_USER use who doesn’t have access to the Australian players rows can insert an Australian Player

EXECUTE AS USER  = 'BCCI_USER'
	INSERT INTO dbo.Players (Name, Country, UserName)
	Values('Glenn McGrath', 'Australia', 'CA_USER')
REVERT

Let us execute the following statement to see whether the BCCI_USER can see the newly inserted Australian player record by him

EXECUTE AS USER  = 'BCCI_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security BCCI_USER row access After Insert

From the result it is clear that BCCI_USER doesn’t have the access to the record Australian player record which he has inserted

Let us see whether the CA_USER can see the Australian player record which the BCCI_USER has inserted

EXECUTE AS USER  = 'CA_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security CA_USER row access After Insert

From the result we can see that the CA_USER has access to the Australian Player record which BCCI_USER has inserted.

So from the above example we can see that a FILTER predicate is not blocking the user from INSERTING a record which after insert is filtered by it for that user for any operation.

Don’t worry to avoid such behavior, we have Block predicate at our disposal. Let us now understand the Block predicate with examples:

BLOCK PREDICATE

Let’s add the AFTER INSERT BLOCK predicate on the Players table to block user from inserting a record which after insert user doesn’t have access to it.

Execute the below statement to alter the above Security policy to add the AFTER INSERT BLOCK predicate.

ALTER SECURITY POLICY PlayersSecurityPolicy
 ADD BLOCK PREDICATE dbo.PlayersPredicateFunction(UserName)
	 ON dbo.Players AFTER INSERT

Here for the AFTER INSERT BLOCK PREDICATE we are using the same predicate function which we have used to filter the records by the FILTER PREDICATE.

Basically, the AFTER INSERT BLOCK Predicate blocks user from inserting a record which after insert doesn’t satisfy predicate function. In other words from this example perspective the AFTER INSERT BLOCK predicate blocks the user from inserting a record which after insert user doesn’t have access to it.

Let us execute the following statement to see whether the user BCCI_USER who doesn’t have access to the Australian players rows can insert an Australian Player

EXECUTE AS USER  = 'BCCI_USER'
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Adam Gilchrist', 'Australia', 'CA_USER')
REVERT

RESULT:

Msg 33504, Level 16, State 1, Line 2
The attempted operation failed because the target object ‘CRICKET.dbo.Players’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

From the result we can see that BLOCK Predicate is blocking a BCCI_USER user from inserting a record which after insert user doesn’t have access to it.

New Catalog Views/DMVs for the Row level security

Following are the two new catalog views introduced for Row level security in Sql Server 2016

  1. sys.security_policies
    This catalog view returns all the Security Policies in the database

    Execute the following statement to get all the security policies in the database with important security policy attributes/columns

    SELECT Name, object_id, type, type_desc,
    is_ms_shipped,is_enabled,is_schema_bound
    FROM sys.security_policies
    

    RESULT:
    Sys security_Policies Catalog views

  2. sys.security_predicates
    This catalog view returns all the Security Predicates in the database

    SELECT * 
    FROM sys.security_predicates
    

    RESULT:
    sys Security_Predicates Catalog View

New Features in Sql Server 2016

Following are the some of the new features of the Sql Server 2016 which I have blogged. Click on the feature name to know it in detail with extensive list of examples:

1. DROP IF EXISTS Statement in Sql Server 2016

In Sql Server 2016, IF EXISTS is the new optional clause introduced in the existing DROP statement. Basically, it checks the existence of the object, if the object does exists it drops it and if it doesn’t exists it will continue executing the next statement in the batch. Basically it avoids writing if condition and within if condition writing a statement to check the existence of the object.

In Sql Server 2016 we can write a statement like below to drop a Stored Procedure if exists.

 
--Drop stored procedure if exists
DROP PROCEDURE IF EXISTS dbo.WelcomeMessage

In Sql Server 2016 we can write a statement like below to drop a Table if exists.

 
--Drop table Customer if exists
DROP TABLE IF EXISTS dbo.Customers

2. STRING_SPLIT function in Sql Server 2016

STRING_SPLIT is one of the new built-in table valued function introduced in Sql Server 2016. This table valued function splits the input string by the specified character separator and returns output as a table.

SYNTAX:

STRING_SPLIT (string, separator)

Where string is a character delimited string of type CHAR, VARCHAR, NVARCHAR and NCHAR.
Separator is a single character delimiter by which the input string need to be split. The separator character can be one of the type: CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).

Result of this function is a table with one column with column name as value.

EXAMPLE: This example shows how we can use STRING_SPLIT function to splits the comma separated string.

SELECT * 
FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',',')

RESULT:
Sql STRING_SPLIT Function Example 1

To understand STRING_SPLIT function with extensive list of examples you may like to go through the article: STRING_SPLIT function in Sql Server 2016

3. GZIP COMPRESS and DECOMPRESS functions in Sql Server 2016

COMPRESS and DECOMPRESS are the new built in functions introduced in Sql Server 2016.

COMPRESS function compresses the input data using the GZIP algorithm and returns the binary data of type Varbinary(max).

DECOMPRESS function decompresses the compressed input binary data using the GZIP algorithm and returns the binary data of type Varbinary(max). We need to explicitly cast the output to the desired data type.

These functions are using the Standard GZIP algorithm, so a value compressed in the application layer can be decompressed in Sql Server and value compressed in Sql Server can be decompressed in the application layer.

Let us understand these functions with examples:

Example: Basic Compress and Decompress function examples

SELECT COMPRESS ('Basavaraj')

RESULT:
0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000

Let us decompress the above compressed value using the DECOMPRESS function by the following script

SELECT DECOMPRESS(
 0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000)

RESULT:
0x42617361766172616A

From the above result we can see that the result of the DECOMPRESS function is not the actual value but instead it is a binary data. We need to explicitly cast the result of the DECOMPRESS function to the datatype of the string which is compressed.

Let us cast the result of the DECOMPRESS function to Varchar type by the following statement.

SELECT CAST(0x42617361766172616A AS VARCHAR(MAX))

RESULT:
Basavaraj

4. SESSION_CONTEXT in Sql Server 2016

In .Net we have Session object which provides a mechanism to store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session. With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT built in function.

EXAMPLE: This example demonstrates how we can set the session context key named EmployeeId with it’s value and retrieving this set keys value.

--Set the session variable EmployeeId value
EXEC sp_set_session_context 'EmployeeId', 5000
--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

RESULT:
SessionContext Sql Example 1 1

5. Compare Execution Plans in Sql Server 2016

Comparing two execution plans is one of the new feature which is getting introduced in Sql Server 2016. This will be one of the good addition to the Sql Server features set. Many a time we come across a scenario where we need to compare the two execution plans. For example some time we want to see what is the difference in the execution plan after making some changes to it. Sometimes we come across a scenario where we observe that some stored procedure is perfectly working fine in development/System test environment but not in the production environment. In such scenario comparing the Dev/QA execution plan with production execution plan gives us the clue on what is going wrong. Without this feature currently we open the two execution plans in separate window and then we manually compare, it is tedious to figure out quickly what is going wrong.

With this new feature we can comapre two execution plans and their properties as shown in the below image

Execution Plan Comparision with Property window

6. Live Query Statistics in Sql Server 2016

Live Query Statistics is one of the new feature introduced in Sql Server 2016, it basically provides the real-time live execution plan of an active running query to the Developer/DBA.

This SSMS feature is very helpful in checking the query execution progress for a long running queries, currently we don’t know for a long running queries where it is stuck or which specific operator is taking long time or how much percentage is completed or approximately how much extra duration required to complete it etc. This feature provides a way to know all these at any given point during the query execution.
It also helps in debugging the queries without needing to wait for the completion of the query execution. As soon as the query execution starts we can see the real-time live execution plan with moving dotted lines between the operators, operator execution progress, overall query execution progress etc.

To get the live query execution plan, just like Actual Execution plan option we need to select the Include Live Query Statistics option as shown in the below image before executing the query

Include Live Query Statistics

Below GIF animation shows an example of live execution plan where we can see moving dotted lines between the operators, operator execution progress, overall query execution progress etc

Live Query Statistics Example 1
*Double click on the image to get the enlarged view

7. Native JSON Support in Sql Server 2016

Native JSON (Java Script Object Notation) support is one of the new feature that is coming with Sql Server 2016.

JSON implementation in Sql server is on the similar lines as that of XML. One major difference is: JSON doesn’t have native JSON data type like the XML data type. We can store JSON data in regular NVARCHAR/VARCHAR column.

Below are the main JSON features introduced in Sql Server 2016. Click on the link to understand each of these features with an extensive list of examples.

8. Temporal Tables in Sql Server 2016

Temporal Table is a new type of user defined table introduced in Sql Server 2016. Temporal table is like any other normal tables the main difference is for Temporal Tables Sql Server automatically tracks the full history of the data changes into a separate history table. Because of the tracking of the fully history of the data, it provides a mechanism to know the state of the data at any point in time.

With regular tables we can only know the current/latest state of the data, we will not be able to see the past state of the updated or deleted records. For regular tables if we need to keep track of the history developer need to create triggers and store the data in separate table there is no built in support for it. But with Temporal Tables Sql Server provides the built-in support for tracking the full history of the data and also provides the temporal querying construct FOR SYSTEM_TIME to query historic data at any point in time or for a period of time. Because of this built in support by the database engine to track the history of the data, Temporal Tables are referred as system-versioned temporal tables/system versioned tables.

  • Below are the some of the common uses of the System-Versioned Temporal Tables
  • It provides a mechanism to Audit the data changes as the complete history is maintained
  • Recovering from accidental data changes. For instance if someone has wrongly deleted a record, because of the availability of the history data we can easily recover these deleted records.
  • Maintain a Slowly Changing Dimension (SCD) for decision support applications

9. Row level security in Sql Server 2016

Row level security is one of the new feature introduced in Sql Server 2016, it provides a mechanism to control row level read and write access based on the user’s context data like identity, role/group membership, session/connection specific information (I.e. like CONTEXT_INFO(), SESSION_CONTEXT etc) etc.

The logic to control the access to table rows resides in the database and it is transparent to the application or user who is executing the query. For example a database user executing a query SELECT * FROM Customers may feel that he has complete access to the Customers table as this query is returning the result without any exception, but with row level security in-place we can make the DataBase engine internally change the query something like for example: SELECT * FROM Customers Where AccountManager = USER_NAME().

Parts of Row-Level Security
Parts of Row Level Security
Following are the three main parts of a Row-Level Security

  1. Predicate Function
    A predicate function is an inline table valued schema bound function which determines whether a user executing the query has access to the row based on the logic defined in it.
  2. Security Predicate
    Security Predicate is the one which binds a Predicate Function to the Table.

    There are two types of security predicates

    1. Filter Predicate
      It filters-out the rows from the SELECT, UPDATE or DELETE operation to which user doesn’t have access based on the logic in the Predicate function. This filtering is done silently without notifying or raising any error.
    2. Block Predicate
      It blocks user from INSERT, UPDATE or DELETE operation by explicitly raising the error if the row doesn’t satisfy the predicate function logic.

      There are four types of BLOCK predicates AFTER INSERT, BEFORE UPDATE, AFTER UPDATE and BEFOR DELETE.

  3. Security Policy
    Security policy is a collection of a Security Predicates which are grouped in a single new object called Security Policy.

10. Dynamic Data Masking in Sql Server 2016

Dynamic data masking is one of the new Security Feature introduced in Sql Server 2016. It provides a mechanism to obfuscate or mask the data from non-privileged users. And the users with sufficient permission will have complete access to the actual or un-masked data.

Traditionally, if we see the application layer takes care of masking the data and displaying it. For example: from database layer we will get a clear SSN number like 123-321-4567, but the application will mask and display it to the user as XXX-XXX-4567. With dynamic data masking from database layer only we can return the query result with masked data if user doesn’t have sufficient permission to view the actual/Unmasked data.

Dynamic Data Masking

Dynamic data masking functions/rule can be defined on the table columns for which we need the masked out-put in the query result. It doesn’t change the actual value stored in the column. Masking function is applied on the query result just before returning the data, if user doesn’t have the enough permission to get the un-masked data. But user with db-owner or UNMASK permission will get the un-masked data in the query result for the masked columns. Masked out-put will be of the same data type as the column data type, in that way we can readily use this feature without really needing changes to the application layer.

Following are the four masking functions which can be defined on table column

  1. Default
  2. Email
  3. Partial
  4. Random

To understand Dynamic Data Masking and each of the above four masking functions with extensive list of examples you may like to go through the article: Dynamic Data Masking in Sql Server 2016

11. Query Store in Sql Server 2016

Many a time we come across a scenario where suddenly in production without any release or changes some query which was working perfectly alright till yesterday is taking too long to execute or consuming lot of resources or timing out.

Most of the times such issue are related to execution plan change (commonly referred as Plan Regression). Till yesterday the query was running fine as it was running with good cached execution plan and today a bad plan is generated and cached. Because of this bad cached plan the query which was executing perfectly alright suddenly starts misbehaving.

To identify and fix such performance problems due to the execution plan change the Query Store feature introduced in Sql Server 2016 will be very handy.

Query Store basically captures and stores the history of query execution plans and its performance data. And provides the facility to force the old execution plan if the new execution plan generated was not performing well.

To understand how the Query Store feature comes handy in resolving performance issues with extensive list of examples you may like to read the article Query Store in Sql Server 2016

For a Query Store feature enabled database we can see the Query Store folder in the SSMS with various options like Regressed Queries, Top Resource Consuming Queries etc as shown in the below image:

Query Store folder in the object explorer

As shown in the below image Query Store gives an option to view and compare various execution plans for a query and force the execution plan for the future execution of the query.

Query Store force the execution plan 1

12. Page allocation changes in Sql Server 2016

In the versions of Sql Server prior to Sql Server 2016 by default first 8 pages for the tables were from a mixed extent at one page at a time and subsequent pages were from an uniform extent. Microsoft in the versions of Sql Server prior to 2016 provided an option to override this behavior by means of Trace Flag 1118. If this trace flag is enabled, the first 8 data pages for the tables were also from Uniform Extent instead of Mixed Extent. This flag was helpful to avoid the resource contention issues, especially in the TempDB in the scenario’s where huge number of temp tables were created.

Below is the page allocation mechanism in the TempDb and User Databases of Sql Server 2016

  • TempDB Database
    With Sql Server 2016, TempDb database objects by default will get the pages from the Uniform Extent.And there is no option to over-ride this behaviour. There is no effect of the Trace Flag 1118 on the page allocation behavior.
  • User Databases
    With Sql Server 2016, even the user databases objects by default will get the pages from the Uniform Extent. We can change this behavior by setting the database property MIXED_PAGE_ALLOCATION by using the ALTER DATABASE statement.

Below image explains how the page allocation happens in the version of Sql Server prior to Sql Server 2016 and in Sql Server 2016. In the example demonstrated below each record which is inserted is of 8000 bytes. From the below image we can see that in the versions of Sql Server prior to 2016 insertion of the first record resulted in reserving 16KB space (i.e. 1 8KB data page from mixed extent + 1 8KB Index Allocation MAP(IAM) Page from the mixed extent). For each subsequent record insertion till the 8th record one 8KB data page is allocated from the mixed extent. In Sql Server 2016 we can see that insertion of the first record resulted in reserving 72KB space (i.e. 1 Uniform 64 KB extent + 1 8KB Index Allocation MAP(IAM) Page from the mixed extent). And the subsequent 7 records insertion is using the remaining 7 pages from the uniform extent allocated during the first record insertion.

Comparision of Page allocation in TempDB

To understand Page allocation changes in Sql Server 2016 with extensive list of examples you may like to go through the article: Page allocation changes in Sql Server 2016

13. DATEDIFF_BIG Function in Sql Server 2016

DATEDIFF_BIG function like DATEDIFF function returns the difference between two dates. The difference between these two functions is the return type. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.

Example: Below example demonstrates how DATEDIFF and DATEDIF_BIG functions behave differently when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff' 

RESULT:
DATEDIFF_BIG Sql Example

So, if we know that sometime the difference between two dates is going to cross the INT max value then we have to use the DATEDIFF_BIG function

SUMMARY

Following are the some of the new features of the Sql Server 2016 which I have blogged. Click on the feature name to know it in detail with extensive examples:

New features in SQL SERVER 2016

Temporal Tables in Sql Server 2016 Part 4: DDL operations on the System-Versioned Temporal Table

This is the fourth article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article will cover DDL operations on the System-Versioned Temporal Table with extensive list of examples

Let us create a Temporal Table by using the following script:

 
--Create demo database if it is not present
IF DB_ID('SqlhintsTemporalDemoDB') IS NULL
BEGIN   
    CREATE DATABASE SqlhintsTemporalDemoDB
END
GO
USE SqlhintsTemporalDemoDB
GO
--Create System-Versioned Temporal Table
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START
            HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END 
            HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory))

The above script will create the Temporal Table dbo.Customer and the corresponding History Table dbo.CustomerHistory as shown in the following image

SSMS View of Temporal and History Table

Let us try to add a column City to the temporal table by executing the following script

ALTER TABLE dbo.Customer ADD City NVARCHAR(50) 

Post executing the above statement, check the columns in the Temporal and History table

Adding a column to the Temporal Table

We can see that adding a column to the Temporal Table adds the column to the History Table too

Let us try to drop the newly added column by the following script

ALTER TABLE dbo.Customer DROP COLUMN City

Post executing the above statement, check the columns in the Temporal and History table

Dropping a column from a Temporal Table

We can see that dropping a column from the Temporal Table drops the column from the history table too

Let us try to add a column directly to the History table by executing the following script

ALTER TABLE dbo.CustomerHistory ADD Country NVARCHAR(50)

RESULT

Msg 13550, Level 16, State 1, Line 1
Add column operation failed on table ‘SqlhintsTemporalDemoDB.dbo.CustomerHistory’ because it is not supported operation on system-versioned temporal tables.

From the above result we can see that when SYSTEM-VERSIONING is ON we can’t perform DDL operations on the HISTORY table but we can add indexes and default constraint on the History table.

History table can’t have constraints like : Primary key, foreign key, table or column constraints. Properties like Period definition, Identity definition, Indexes, Statistics, Check Constraints, Triggers, Permission, Partitioning Configuration is not replicated from the temporal/current table to the history table when it is created.

Below image shows that the primary key is not replicated from the Temporal Table to History Table when it is created.

Primar Key is not propogated to the history table

One more important point to note that the history table by default is page compressed

Dropping a Temporal Table

Let us try to drop a Temporal Table by executing the below statement

DROP TABLE dbo.Customer

RESULT

Msg 13552, Level 16, State 1, Line 1
Drop table operation failed on table ‘SqlhintsTemporalDemoDB.dbo.Customer’ because it is not supported operation on system-versioned temporal tables.

From the above result we can see that when SYSTEM-VERSIONING is ON we can’t drop a Temporal table

Disabling System Versioning

We can disable system versioning by executing the following statement:

ALTER TABLE dbo.Customer SET (SYSTEM_VERSIONING = OFF)

After executing the above statement the System Versioning is disabled and Temporal and History table will become like any other regular table as shown in the following image:

Disabling System Versioning

Now try to update the Period columns in the dbo.Customer table

UPDATE dbo.Customer
SET 	StartTime = GETUTCDATE()
WHERE Id = 1

RESULT

Msg 13537, Level 16, State 1, Line 19
Cannot update GENERATED ALWAYS columns in table ‘SqlhintsTemporalDemoDB.dbo.Customer’.

From the above result it is clear that even after disabling the SYSTEM_VERSIONING, we will not be able to update the PERIOD column values. To allow the PERIOD column value update we have to drop the PERIOD definition by executing the following statement

ALTER TABLE dbo.Customer
DROP PERIOD FOR SYSTEM_TIME

Now once again try updating by executing the following script

UPDATE dbo.Customer
SET 	StartTime = GETUTCDATE()
WHERE Id = 1

RESULT
Updating Temporal Table PERIOD column value

Now try to drop the dbo.Customer and dbo.CustomerHistory tables by executing the following script:

DROP TABLE dbo.Customer
GO
DROP TABLE dbo.CustomerHistory

RESULT
Dropping Regular Tables

In Summary below is the script to drop System-Versioned Temporal Tables

--First disable the system versioning
ALTER TABLE dbo.Employee
    SET (SYSTEM_VERSIONING = OFF)
GO
--Drop Period definition.
--This will allow manipulations on the PERIOD columns
ALTER TABLE dbo.Employee
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Employee and EmployeeHistory tables
--(To drop these tables, dropping PERIOD definition is optional)
DROP TABLE dbo.Customer
DROP TABLE dbo.CustomerHistory

Temporal Tables in Sql Server 2016 Part 3: Enabling System Versioning for an Existing Regular Table

This is the third article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article explains how we can enable system versioning on an existing regular table and link it to a new or existing table as history table.

Let us first create demo database by using the following script

IF DB_ID('SqlhintsTemporalDemoDB') IS NULL
BEGIN
    CREATE DATABASE SqlhintsTemporalDemoDB
END
GO
USE SqlhintsTemporalDemoDB
GO

Enabling System-Versioning for an existing regular table and linking it to a new history table

Let us first create a regular Product table with Sample data by executing the following script:

CREATE TABLE dbo.product
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL
)
GO
INSERT INTO dbo.Product VALUES( 1, 'Television')
GO

Now try to enable the system versioning by executing the below statement:

ALTER TABLE dbo.Product
SET (SYSTEM_VERSIONING = ON)

RESULT

Msg 13510, Level 16, State 1, Line 1
Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

From the result it is clear that to enable system-versioning the table needs to have two period DATETIME2 columns with period definition in it.

Let’s try to add the two datetime2 datatype period columns and the period definition by the following script

ALTER TABLE dbo.Product
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
              HIDDEN  NULL,
   EndTime DATETIME2 GENERATED ALWAYS AS ROW END
              HIDDEN  NULL,
   PERIOD FOR SYSTEM_TIME (StartTime, EndTime)  

RESULT

Msg 13587, Level 16, State 1, Line 1
Period column ‘StartTime’ in a system-versioned temporal table cannot be nullable.

From the result it is clear that the period columns shouldn’t be nullable column.

Now try to execute the below statement to add two datetime2 type NOT NULL period columns (if null constraint is not specified for period columns they are considered as NOT NULL) and the period definition:

  
ALTER TABLE dbo.Product
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
   EndTime   DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
   PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

RESULT

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘StartTime’ cannot be added to non-empty table ‘Product’ because it does not satisfy these conditions.

From the result it is clear that we are not allowed to add NOT NULL columns to an existing table with data in it.

Let us now try to add NOT NULL PERIOD columns to the existing table with DEFAULT value by executing the following statement:

ALTER TABLE dbo.Product
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
  HIDDEN DEFAULT GETUTCDATE(),
 EndTime  DATETIME2 GENERATED ALWAYS AS ROW END
  HIDDEN DEFAULT 
     CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
 PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

We have successfully added the PERIOD columns and PERIOD definition to the Product table, now enable the system versioning by executing the below statement. Here we are mentioning the new history table name as dbo.ProductHistoy. Specifying History table name is optional, if we don’t specify the history table name then Sql Server creates a new history table with name like MSSQL_TemporalHistoryFor

ALTER TABLE dbo.Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory))
GO

After executing the above statement, if we look into the SSMS window we can see that a dbo.Product table is marked as System-Versioned table. Inside this table we can see a new nested table dbo.ProductHistory table created and marked as History Table as shown below.

SSMS View of Temporal Table Product 1

SUMMARY

Below is the script for enabling System-Versioning for an existing regular table and linking it to a new history table

BEGIN TRAN
--Add two PERIOD datetime2 columns and PERIOD definition 
ALTER TABLE dbo.Product
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
   HIDDEN NOT NULL DEFAULT GETUTCDATE(),
 EndTime   DATETIME2 GENERATED ALWAYS AS ROW END
   HIDDEN  NOT NULL DEFAULT 
     CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
 PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
--Enable System Versioning
ALTER TABLE dbo.Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory))
COMMIT TRAN

CLEAN-UP

Let us drop the Product and ProductHistory tables by the following script

--Disable the system versioning
ALTER TABLE dbo.Product
    SET (SYSTEM_VERSIONING = OFF)
GO
--Drop Period definition
ALTER TABLE dbo.Product
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Product and ProductHistory tables
DROP TABLE dbo.Product
DROP TABLE dbo.ProductHistory

Enabling System-Versioning for an existing regular table and linking it to an existing history table

Let us create a regular Product and ProductHistory table by using the following script:

---Create a regular Product table
CREATE TABLE [dbo].[Product](
	[ProductId] [int] NOT NULL PRIMARY KEY CLUSTERED ,
	[Name] [nvarchar](100) NOT NULL,	
	[StartTime] DATETIME NOT NULL,
	[EndTime] DATETIME NOT NULL,
	[Price] [float] NULL
) 
GO
---Create a regular ProductHistory table
CREATE TABLE [dbo].[ProductHistory](
	[ProductHistoryId] [int] IDENTITY(1,1) NOT NULL ,
	[ProductId] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[StartTime] DATETIME NOT NULL,
	[EndTime] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductHistory] 
             PRIMARY KEY  CLUSTERED ([ProductHistoryId]), 
	CONSTRAINT FK_ProductHistory_Product 
             FOREIGN KEY ([ProductId])
	REFERENCES Product([ProductId])
) ON [PRIMARY]
GO

Let us execute the following statement to enable System-Versioning for the Product table and link it to an existing table ProductHistory as the history table

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )
GO

RESULT

Msg 13510, Level 16, State 1, Line 23
Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

From the result it is clear that to enable system-versioning table needs to have PERIOD definition

Let us try to add PERIOD definition to the table by using the following script

ALTER TABLE dbo.Product
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)  

RESULT

Msg 13501, Level 16, State 3, Line 31
Temporal generated always column ‘StartTime’ has invalid data type.

From the result it is clear that PERIOD column has invalid data type

Let us change the data type of the PERIOD columns to DATETIME2 type and then add the PERIOD definition

--Alter StartTime Column type to DATETIME2
ALTER TABLE dbo.Product
ALTER COLUMN  [StartTime] DATETIME2 NOT NULL
--Alter EndTime Column type to DATETIME2
ALTER TABLE dbo.Product
ALTER COLUMN  [EndTime] DATETIME2 NOT NULL
GO
--Add the period definition
ALTER TABLE dbo.Product
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

Now try to enable System-Versioning

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )

RESULT

Msg 13515, Level 16, State 1, Line 55
Setting SYSTEM_VERSIONING to ON failed because history table ‘SqlhintsTemporalDemoDB.dbo.ProductHistory’ has custom unique keys defined. Consider dropping all unique keys and trying again.

From the error it is clear that System-Versioning can’t be enabled when we primary key on the history table.

Let us drop the primary key from ProductHistoryId column by the following script

ALTER TABLE ProductHistory
DROP CONSTRAINT [PK_ProductHistory]

Now try to enable System-Versioning

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )

RESULT

Msg 13516, Level 16, State 1, Line 67
Setting SYSTEM_VERSIONING to ON failed because history table ‘SqlhintsTemporalDemoDB.dbo.ProductHistory’ has foreign keys defined. Consider dropping all foreign keys and trying again.

From the above error it is clear that we can’t enable SYSTEM_VERSIONING when history table has foreign key constraints

Let us drop the foreign key constraint using the following script

ALTER TABLE ProductHistory
DROP CONSTRAINT FK_ProductHistory_Product

Now try to enable System-Versioning

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )

RESULT

Msg 13518, Level 16, State 1, Line 83
Setting SYSTEM_VERSIONING to ON failed because history table ‘SqlhintsTemporalDemoDB.dbo.ProductHistory’ has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.

From the error it is clear that we can’t enable system-versioning when the history table has identity columns in it

Let us drop the identity coluimn [ProductHistoryId] from the ProductHistory table using the following script

ALTER TABLE [dbo].[ProductHistory]
DROP COLUMN [ProductHistoryId]

Now try to enable System-Versioning

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )

RESULT

Msg 13523, Level 16, State 1, Line 99
Setting SYSTEM_VERSIONING to ON failed because table ‘SqlhintsTemporalDemoDB.dbo.Product’ has 5 columns and table ‘SqlhintsTemporalDemoDB.dbo.ProductHistory’ has 4 columns.

From the above error it is clear that to enable system versioning the number columns in the Temporal and the history tables should be same.

Let us add price column to the ProductHistory table too

ALTER TABLE [dbo].[ProductHistory]
ADD [Price] [Float]

Now try to enable System-Versioning

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )

RESULT

Msg 13525, Level 16, State 1, Line 115
Setting SYSTEM_VERSIONING to ON failed because column ‘StartTime’ has data type datetime in history table ‘SqlhintsTemporalDemoDB.dbo.ProductHistory’ which is different from corresponding column type datetime2(7) in table ‘SqlhintsTemporalDemoDB.dbo.Product’.

From the error it is clear that even the column data types should match in order to enable system versioning

Let us match ProductHistory tables all the columns data types with the corresponding columns in the Product table

--Alter StartTime Column type to DATETIME2
ALTER TABLE dbo.ProductHistory
ALTER COLUMN  [StartTime] DATETIME2 NOT NULL
--Alter EndTime Column type to DATETIME2
ALTER TABLE dbo.ProductHistory
ALTER COLUMN  [EndTime] DATETIME2 NOT NULL
GO

Now try to enable System-Versioning

ALTER TABLE [dbo].[Product]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory) )

After executing the above statement, if we look into the SSMS window we can see that a dbo.Product table is marked as System-Versioned table. And dbo.ProductHistory table is nested in the dbo.Product table and marked as History Table as shown below.

SMS View of the Temporal Table Product 2
SUMMARY

In summary following are the some of the major list of rules needs to be satified by the existing history table in order for it to be linked by the Temporal table as the history table

  • History Table shouldn’t have Primary Key Constraint
  • History Table shouldn’t have any Foreign key coinstraints
  • History table shouldn’t have any identity column
  • Number of columns in the history table should be same as that of the parent table
  • The columns Ordinal position in the history table should match with the column in the parent table
  • Data type of all the columns in the history tables should match with the columns in the parent table
  • No Triggers/CDC/Change Tracking defined on the History table

Below is the script for enabling System-Versioning for an existing regular table (i.e. dbo.Product) linking it to an existing history table (i.e. dbo.ProductHistory )

BEGIN TRAN
--Add two PERIOD datetime2 columns and PERIOD definition 
ALTER TABLE dbo.Product
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
  HIDDEN NOT NULL DEFAULT GETUTCDATE(),
 EndTime   DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
  DEFAULT CONVERT(DATETIME2,'9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
--Enable System Versioning
ALTER TABLE dbo.Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory))
COMMIT TRAN

CLEAN-UP

Let us drop the Product and ProductHistory tables by the following script

--Disable the system versioning
ALTER TABLE dbo.Product
    SET (SYSTEM_VERSIONING = OFF)
GO
--Drop Period definition
ALTER TABLE dbo.Product
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Product and ProductHistory tables
DROP TABLE dbo.Product
DROP TABLE dbo.ProductHistory

[ALSO READ] New features in Sql Server 2016:

Temporal Tables in Sql Server 2016 Part 2: Querying System-Versioned Temporal Table

This is the second article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article covers the querying of the Temporal tables by new temporal querying clause FOR SYSTEM_TIME and its five sub-clauses in the SELECT…FROM construct. This construct simplifies the querying of the History data and hides the complexity from the users. Even though the temporal table data exists in two tables one is the current table and other is the history table, this construct hides all the complexity of fetching data from these two tables.

Let us create a demo database with Temporal Table dbo.Customer with the following script:

CREATE DATABASE SqlHintsQueryingTemporalDB
GO
USE SqlHintsQueryingTemporalDB
GO
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START
            HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END 
            HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory))

The above script will create the Temporal Table dbo.Customer and the corresponding History Table dbo.CustomerHistory as shown in the following image:

SSMS View of Temporal and History Table

Let us populate the data in the temporal table by performing the following DML operations:

INSERT INTO dbo.Customer VALUES (1,'Basavaraj')
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Biradar' WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj P Biradar'
WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Prabhu Biradar'
WHERE Id = 1
GO

Here I am keeping a delay of 2 minutes between each DML operations by using the WAITFOR DELAY statement.

Below image shows the records in the dbo.Customer and dbo.CustomerHistory tables after executing the above DML statements:

Records in the Temporal and History Table

Below is the depiction of the above DML operations on the Timeline graph:

System Versioning TimeLine Graph

Now we have the Temporal Table with the sample data, let us start understanding one-by-one the five sub-clauses of Temporal querying clause FOR SYSTEM_TIME:

AS OF <datetime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This sub-clause returns a record for each row that was valid at the specified point-in-time. In other words it will bring all the records from the Temporal Table and the history table which meet the following criteria:

StartTime<= @PointInTime AND EndTime > @PointInTime

Let us try to get the state of the data that was valid at ‘2015-12-29 07:51’. This point-in-time in the past is depicted in the below milestone graph by Yellow milestone point circle.

FOR SYSTEM_TIME AS OF TimeLine Graph

Execute the following query to get the records which were valid in the past point-in-time ‘2015-12-29 07:51’ by enabling the execution plan

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2015-12-29 07:51'

RESULT:
FOR SYSTEM_TIME AS OF Example 1

Below is the execution plan of this query execution.

FOR SYSTEM_TIME AS OF Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME AS OF clause internally retrieving the data from both the Temporal Table and History Table and returning the UNION of the results from both the tables. One more observation from the execution plan is that on the temporal table we seeing Clustered Index scan, it is because our queries filter predicate is based on the Period columns but we don’t have index on these columns. So if we know that we are going to query by the Period columns it is better to add an index on these columns and by default Sql Server is adding an index on the period columns in the History table.

Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @PointInTime DATETIME = '2015-12-29 07:51'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime<= @PointInTime  AND EndTime > @PointInTime 
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime<= @PointInTime  AND EndTime > @PointInTime 

RESULT
Equivalent of FOR SYSTEM_TIME AS OF

FROM <StartDateTime> TO <EndDateTime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause returns all the row versions which were some time active between <StartDateTime> and <EndDateTime> (i.e. all the row versions where StartTime is before the input <EndDateTime> and EndTime after the input parameter <StartDateTime>). In other words it will return all the row versions which match the below criteria:

StartTime < <EndDateTime> AND EndTime > <StartDateTime>

Below is an example of a FROM … TO … clause:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME FROM @StartDateTime TO @EndDateTime

RESULT
FOR SYSTEM_TIME FROM TO Example

Below is the execution plan of this query execution.

FOR SYSTEM_TIME FROM TO Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME FROM … TO … clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime < @EndDateTime AND EndTime > @StartDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime < @EndDateTime AND EndTime > @StartDateTime

RESULT
Equivalent of FOR SYSTEM_TIME FROM TO

BETWEEN <StartDateTime> AND <EndDateTime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause is same as the previous FROM <StartDateTime> TO <EndDateTime> clause except that it also includes the records which became active on <EndDateTime> (i.e. StartTime = <EndDateTime>). In other words it will return all the row versions which match the below criteria:

StartTime <= <EndDateTime> AND EndTime > <StartDateTime>

Let us execute the below query which is using the BETWEEN clause with the same @StartDateTime AND @EndDateTime as in the FROM clause example

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
        @EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME BETWEEN @StartDateTime AND @EndDateTime

RESULT
FOR SYSTEM_TIME BETWEEN AND Example

From the result we can observe that BETWEEN clause is returning one extra record whose StartTime = @EndDateTime compared to the FROM clause

Below is the execution plan of this query execution.
FOR SYSTEM_TIME BETWEEN AND Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME BETWEEN … TO … clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime <= @EndDateTime AND EndTime > @StartDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime <= @EndDateTime AND EndTime > @StartDateTime

CONTAINED IN ( <StartDateTime> , <EndDateTime>) sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause returns only the row versions which became active and ended in the specified date range. In other words it will return all the row versions which match the below criteria:

StartTime >= <StartDateTime> AND EndTime <= <EndDateTime>

Let us execute the below query which is using the CONTAINED clause with the same @StartDateTime AND @EndDateTime which are used in the previous BETWEEN clause example

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME CONTAINED IN (@StartDateTime, @EndDateTime)

RESULT
FOR SYSTEM_TIME CONTAINED Example
Below is the execution plan of this query execution.
FOR SYSTEM_TIME CONTAINED Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME CONTAINED IN clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime >= @StartDateTime AND EndTime <= @EndDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime >= @StartDateTime AND EndTime <= @EndDateTime

FOR SYSTEM_TIME ALL

This clause returns all the row versions from both the Temporal and History table

Let us execute the below query which is using the ALL clause:

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME ALL

RESULT
FOR SYSTEM_TIME ALL Example
Below is the execution plan of this query execution.
FOR SYSTEM_TIME ALL Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME ALL clause internally retrieving the data from both the Temporal Table and History Table and returning the UNION of the both the results. Below is an equivalent query of this query, which we would have written if this construct was not available:

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory

[ALSO READ] New features in Sql Server 2016: