SQL Server 2016 – Adding Temporal Tables to Existing Application

Is it possible to add temporal tables to 2-3 key tables in the application, without changing the PHP code?

You should be able to achieve that goal (assuming the application is written in a way that adding additional columns to the table doesn't cause a problem). Of course, test, test, test.

Using the example code provided in Greg Larsen's post Altering an Existing Table to Support Temporal Data, let's work through an example.

Here is our current table.

DROP TABLE IF EXISTS dbo.Product
DROP TABLE IF EXISTS dbo.ProductHistory

CREATE TABLE dbo.Product (
    ID INT Identity
    ,ProductName VARCHAR(50)
    ,ProductPrice DECIMAL(20, 2)
    );

INSERT INTO dbo.Product (ProductName, ProductPrice)
VALUES ('Widget',33.49),('Doo-Hickey',21.76),('Thing-A-Ma-Jig',20.16);

SELECT * FROM dbo.Product

| ID | ProductName    | ProductPrice |
|----|----------------|--------------|
| 1  | Widget         | 33.49        |
| 2  | Doo-Hickey     | 21.76        |
| 3  | Thing-A-Ma-Jig | 20.16        |

We now want to track versions.

In order to start collecting historical information for my dbo.Product table I will need to alter the table so it will support temporal data. A SQL Server 2016 temporal table requires a table to have a primary key and a couple for date/time columns. The two date/time columns will be used to determine the period of time for when the record is valid. Therefore the first thing I need to do is alter my Product table to meet the temporal data table requirements. To do that I will run the following code:

ALTER TABLE dbo.Product 
    ADD CONSTRAINT PK_ProductID PRIMARY KEY (ID),
    BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT SYSUTCDATETIME()
,
    EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
    PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);

There is some very good information in Creating a System-Versioned Temporal Table that addresses Altering Non-Temporal Table to be System-Versioned Temporal Table.

When converting an existing table, consider using the HIDDEN clause to hide the new PERIOD columns to avoid impacting existing applications that are not designed to handle new columns.

The next step in setting up a temporal table is to identify a history table that goes along with my dbo.Product table. To do that I will run the following code:

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

Now the Product table looks like this

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 33.49        | 2017-09-29 14:28:04.8327739 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |

You also find that a new ProductHistory table has been created that looks just like the Product table. The ProductHistory table is currently empty because we haven't updated or deleted anything from the Product table

Now, let's run an update to generate some history

UPDATE dbo.Product 
SET ProductPrice = 34.65
WHERE ProductName = 'Widget';

select * from dbo.Product
select * from dbo.Producthistory

The product table has the current data

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |

And now the ProductHistory table has a versioned row

| ProductName | ProductPrice | BeginDate                   | EndDate                     |
|-------------|--------------|-----------------------------|-----------------------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 |

You can even add a column to the Product table and the ProductHistory table automatically gets the new column added as well

ALTER TABLE dbo.Product ADD ProductColor VARCHAR(10)

Product

| ProductName    | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|----------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 | NULL         |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |

ProductHistory

| ProductName | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|-------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 | NULL         |

Are there risks of compatibility or performances?

You would certainly need to test this in a development environment first. You may have unique situations that have an impact on your decision to use Temporal tables.

Here are a few important links with additional information regarding Temporal tables

Getting Started with System-Versioned Temporal Tables

Temporal Table Considerations and Limitations

Manage Retention of Historical Data in System-Versioned Temporal Tables

Stopping System-Versioning on a System-Versioned Temporal Table