SQL Server 2016: Adding temporal tables to existing application

I have a big PHP application, developed by an external company, which is being completed right now; I cannot ask for changes to it.
It uses a database Microsoft SQL Server 2016, which is accessed by some users via SSMS, including me, and by some scheduled tasks at specific times of day.
Sometimes I find unexpected content in some tables, and it is frustrating because I don't know when it got there.
I just discovered about temporal tables: knowing the exact date and time when a record changes would be extremely useful.

Is it possible to add temporal tables to 2-3 key tables in the application, without changing the PHP code? Are there risks of compatibility or performances?

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

StackExchange Database Administrators Q#187250, answer score: 5