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?
1 Answer 1
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
Explore related questions
See similar questions with these tags.