Tag Archives: Sql 2016

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

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , ,

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:

Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

Sql Server, Sql Server 2016, , , , , , , , , , ,

This article gives an introduction to the DATEDIFF and DATEDIFF_BIG functions by listing out the differences and similarities between them.

[ALSO READ]: How to get difference between two dates in Years, Months and days in Sql Server

DATEDIFF Function DATEDIFF_BIG Function
This function is available from very old versions of Sql Server. This function is introduced in Sql Server 2016
This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate
Syntax:
DATEDIFF( datepart, startdate, enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

Syntax:
DATEDIFF_BIG(datepart,startdate,enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

This functions return value data type is INT This functions return value data type is BigINT
Example: Below is an example getting difference between two dates in days using DATEDIFF function:

SELECT DATEDIFF(DAY,
 '12/01/2015','12/02/2015')
 AS 'Difference in days'

RESULT:
Difference in days
——————
1

Example: Below is an example getting difference between two dates in days using DATEDIFF_BIG function:

SELECT DATEDIFF_BIG(DAY,
 '12/01/2015','12/02/2015')
  AS 'Difference in days'

RESULT:
Difference in days
——————–
1

Example: This example demonstrates the behavior of the DATEDIFF function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(MILLISECOND,
 '12/01/2015','12/30/2015')
 AS 'Difference in MILLISECOND'

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.

 Example: This example demonstrates the behavior of the DATEDIFF_BIG function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF_BIG(MILLISECOND,
 '12/01/2015','12/30/2015') 
AS 'Difference in MILLISECOND'

RESULT:
Difference in MILLISECOND
————————-
2505600000

The minimum and maximum value that this function can return is: -2,147,483,648 and +2,147,483,647 The minimum and maximum value that this function can return is: -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

ALSO READ

DATEDIFF_BIG Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , ,

DATEDIFF_BIG is one of the new function introduced in Sql Server 2016. It gives the difference between the two dates in the units specified by the DatePart parameter and the returned unit is of type bigint. This function like DATEDIFF function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate. 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.

Syntax:

DATEDIFF_BIG ( datepart , startdate , enddate )

[ALSO READ] Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

The request for this new function was submitted on the Microsoft connect site some time back in 2008 by Erland Sommarskog.

With DATEDIFF function for milliseconds the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. This is because the return type of the DATEDIFF function is INT and INT datatypes Min and Max value is: -2,147,483,648 to +2,147,483,647. But with DATEDIFF_BIG function the maximum difference is very high as the return type is bigint and it’s Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Example 1: Basic example

SELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+1) 'DateDiff Big'

RESULT:
DATEDIFF_BIG Basic example

ALSO READ: How to get difference between two dates in Years, Months and days in Sql Server

Example 2: 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

[ALSO READ]: