By: Jared Westover | Updated: 2023-07-27 | Comments (4) | Related: > Change Data Capture
Problem
One difficulty in our modern world is all the choices. Your favorite streaming service offers hundreds of shows to choose from. Approaches to problem-solving in SQL Server are the same, with numerous options. Do you need help deciding on a technique for tracking history in your tables? You've seen a few options but don't know which one to choose. Stay tuned for a comparison of two popular methods.
Solution
In this article, I want to look at two different methods for accomplishing the same goal, tracking table history. First, we'll look at why you generally want to track a table's history. Continuing, I'll present two typical methods you see, triggers and temporal. Does one perform better than the other? What are some drawbacks of each approach? These are a couple of questions I'll answer. By the end of this article, you can make a better decision regarding implementing a history-tracking method.
Tracking Table History
Why do you need to track history in a table? I can think of two primary reasons. You can likely come up with others. First, your organization wants to know what's changing and who made the change. Whenever someone deletes or changes data, the first question is: Can we tell who did this? Getting to the bottom of this is helpful when you seek to stop someone from accidentally doing it again. Now, if the person does the same harmful behavior, there may be consequences. Having the history is nice because we can likely bring the data back to life or provide it to the end user to re-add.
The second reason dovetails off the first: legal or compliance regulations may require tracking data modification. For example, you could have a SOC 2 compliance standard calling for monitoring all data modifications. This reason could tie in with needing historical data for reporting.
Tools for Tracking
SQL Server offers multiple tools for tracking modifications in tables. A few are Change Data Capture (CDC) and Change Tracking. The latter doesn't track the before and after values like CDC. Another option might be to incorporate the logic into whatever method you're using to modify the data in the first place, either with stored procedures or entity framework. This approach takes forethought in planning when building the application, which we all know doesn't always happen. Additionally, you can use one of the many third-party applications. However, this article explores triggers and temporal tables.
Temporal Tables
When discussing temporal tables, people sometimes hear temporary tables. But they are nothing alike. What's a system-versioned temporal table? Microsoft defines them as a user table with a full history of data changes, allowing easy point-in-time analysis. Microsoft introduced them in SQL Server 2017, and they provided a great alternative to triggers or CDC for history tracking.
It's a breeze to enable temporal, which is one reason I like them. The code would look something like what's below. A primary key must be defined on the temporal table, or SQL returns an ugly error message.
source: https://www.mssqltips.com
CREATE TABLE dbo.MySpecialTable
(
Id INT NOT NULL,
Column1 NVARCHAR(250) NULL,
Column2 NVARCHAR(250) NULL,
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime),
CONSTRAINT PK_EmployeeTemporal_Employee_Id
PRIMARY KEY CLUSTERED (Id),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MySepcialTable_History));
GO
When you add a new column to the non-temporal table, SQL automatically adds it to the temporal table. A downside of the temporal table is the lack of customization. For example, if you allow users to modify data without checking if something changes, SQL still inserts a row into the history table. You can argue that you should fix the underlining code causing duplicate records; I agree.
Also, if a product implements a retention policy where client data removal occurs every 20-30 days, it's a huge pain to turn off the versioning and back on. Imagine performing this operation on 100+ tables.
Using Triggers
If you've been in the database game for a while, you have an opinion on triggers. Some people hate them, but others can't get enough. One area where you'll see people generally agree is when it comes to auditing. Microsoft defines a trigger as a special stored procedure that automatically runs when an event occurs in the database server. This article focuses on DML triggers that execute after an UPDATE or DELETE.
Simple triggers are easy to create. The code below creates a trigger with similar functionality to the above temporal table.
--source: https://www.mssqltips.com
CREATE OR ALTER TRIGGER dbo.TR_MySpecialTable_Insert_History
ON dbo.MySpecialTable
AFTER UPDATE, DELETE
AS
BEGIN
INSERT INTO dbo.EmployeeTrigger_History
(
Id,
Column1,
Column2,
SysStartTime,
SysEndTime
)
SELECT Id,
Column1,
Column2,
SysStartTime,
GETUTCDATE()
FROM DELETED;
END;
GO
One thing I don't like about triggers is that you need to maintain them. For example, if you add a new column to the non-history table, you need to add it to the history and update the triggers logic to account for it. If your development team creates T-SQL regularly, this might be a non-issue. Additionally, it's easy to manage if you only track history on 5-20 tables.
Triggers are also a gateway to incorporating excessive business logic. When business folks learn about triggers, they become the duct tape of databases. We don't need to fix the application code; add a trigger to do it. However, for our comparison in the article, we'll only focus on keeping track of history.
Building the Dataset
Let's create decent-size tables for testing with one million rows. The code below creates four tables and populates the non-history ones. One nice thing about temporal is that the history table uses page compression. With that in mind, we'll enable page compression on our TriggerHistory table. Additionally, on the EmployeeHistory_Trigger table, I'll add a clustered index on the SysStartTime and SysEndTime columns.
--source: https://www.mssqltips.com
USE [master];
GO
IF DATABASEPROPERTYEX( 'HistoryDemo',
'Version'
) IS NOT NULL
BEGIN
ALTER DATABASE HistoryDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE HistoryDemo;
END;
GO
CREATE DATABASE HistoryDemo;
GO
ALTER DATABASE HistoryDemo SET RECOVERY SIMPLE;
GO
USE HistoryDemo;
GO
DECLARE @UpperBound INT = 1000000;
;WITH cteN (Number)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_columns AS s1
CROSS JOIN sys.all_columns AS s2
)
SELECT [Number]
INTO dbo.Numbers
FROM cteN
WHERE [Number] <= @UpperBound;
CREATE UNIQUE CLUSTERED INDEX CIX_Number
ON dbo.Numbers ([Number])
WITH (FILLFACTOR = 100);
CREATE TABLE dbo.EmployeeTemporal
(
Id INT IDENTITY(1, 1) NOT NULL,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL
DEFAULT 1,
IsDeleted BIT NOT NULL
DEFAULT 0,
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime),
CONSTRAINT PK_EmployeeTemporal_Employee_Id
PRIMARY KEY CLUSTERED (Id)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeTemporal_History));
CREATE TABLE dbo.EmployeeTrigger
(
Id INT IDENTITY(1, 1) NOT NULL,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL
DEFAULT 1,
IsDeleted BIT NOT NULL
DEFAULT 0,
SysStartTime DATETIME2 NOT NULL
DEFAULT GETUTCDATE(),
SysEndTime DATETIME2 NOT NULL
DEFAULT CONVERT( DATETIME2,
'9999-12-31 23:59:59.9999999'
)
CONSTRAINT PK_EmployeeTrigger_Employee_Id
PRIMARY KEY CLUSTERED (Id)
);
GO
CREATE TABLE dbo.EmployeeTrigger_History
(
Id INT NOT NULL,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL,
IsDeleted BIT NOT NULL,
SysStartTime DATETIME2 NOT NULL,
SysEndTime DATETIME2 NOT NULL
)
WITH (DATA_COMPRESSION = PAGE);
GO
CREATE CLUSTERED INDEX ix_EmployeeTrigger_History
ON dbo.EmployeeTrigger_History (
SysEndTime ASC,
SysStartTime ASC
);
INSERT INTO dbo.EmployeeTemporal
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
)
SELECT TOP (1000000)
CONCAT('E00', n.Number) AS EmployeeNumber,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 6) AS FirstName,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 7) AS LastName,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 1) AS MiddleInitial,
ABS(CHECKSUM(NEWID()) % 100000) + 45000 AS YearlySalary,
CONCAT('M000', ABS(CHECKSUM(NEWID()) % 3) + 1) AS DepartmentCode,
DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + DATEDIFF(DAY, '2020-01-01', '03-31-2023')), '2020-01-01') AS StartDate,
CASE
WHEN (n.Number % 1000) = 0 THEN
0
ELSE
1
END AS IsActive
FROM dbo.Numbers n;
GO
INSERT INTO dbo.EmployeeTrigger
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
)
SELECT
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted FROM dbo.EmployeeTemporal;
GO
CHECKPOINT;
GO
The screenshot below illustrates what our tables look like.
With our tables and data, it's now time to create a trigger. I want a trigger to insert rows into the history anytime someone updates or deletes rows, mimicking the temporal behavior. You could also create two triggers, one for the update and another for the delete. For the demo, we'll keep things simple.
--source: https://www.mssqltips.com
CREATE OR ALTER TRIGGER dbo.TR_Employee_Insert_History
ON dbo.EmployeeTrigger
AFTER UPDATE, DELETE
AS
BEGIN
INSERT INTO dbo.EmployeeTrigger_History
(
Id,
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted,
SysStartTime,
SysEndTime
)
SELECT Id,
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted,
SysStartTime,
GETUTCDATE()
FROM DELETED;
END;
GO
Configure the Performance Test
Without reading further, which method do you think performs better when updating or deleting data? Before writing this, I picked temporal outperforming triggers by a mile.
To test this, I'll first update 10 thousand rows in each non-history table using SQLQueryStress.
--source: https://www.mssqltips.com
UPDATE dbo.EmployeeTrigger
SET YearlySalary = ABS(CHECKSUM(NEWID()) % 100000) + 45000,
IsDeleted = 1,
DepartmentCode = 'M0004',
SysStartTime = GETUTCDATE()
WHERE Id = @trigger;
UPDATE dbo.EmployeeTemporal
SET YearlySalary = ABS(CHECKSUM(NEWID()) % 100000) + 45000,
IsDeleted = 1,
DepartmentCode = 'M0004'
WHERE Id = @trigger;
I'll define the parameter substitution query below. This returns 10 thousand random numbers between one and a million.
--source: https://www.mssqltips.com
SELECT TOP (10000)
Number AS Id
FROM dbo.Numbers
ORDER BY NEWID();
Running the Test
Now it's time to execute each test and review the results. I executed each of these a few dozen times. Below are the typical results.
After testing, temporal tables slightly outperformed triggers. The results are close enough that it's a tie in my book. You could modify the trigger to improve the performance. The performance of the delete operation followed the same pattern, with temporal narrowly outperforming triggers.
| Method | Logical Reads | CPU Time | Elapsed Time |
|---|---|---|---|
| Temporal | 5.60 | .0002 | 1.03 |
| Triggers | 5.66 | .0003 | 1.06 |
Making a Choice
Based on the performance test, it doesn't make a big difference which method you choose. However, as mentioned above, multiple factors go into deciding. For example, does your team hate working with T-SQL? After typing that, it hurts to admit such people exist. Well, triggers might not be the best option. Do you need to track history on hundreds of tables? Again, creating and maintaining triggers add overhead down the road. Maybe you have under 30 tables and don't expect to add any and want the ability to add some customization to the history tracking process. You might consider going with triggers over enabling temporal.
We make decisions based on our past experiences. It's understandable if triggers rubbed you the wrong way, ultimately avoiding them. Let me know in the comments below what method you use to track history in a table.
Key Takeaways
- Both triggers and temporal are valid approaches to tracking table history.
- Triggers allow adding custom logic, which temporal tables lack.
- Temporal tables are easier to add at scale compared to creating triggers.
- Do you have a strict data retention policy in place? Temporal tables add extra complexity because of the need to disable them when deleting data.
Next Steps
- Are you interested in learning more about SQL Server triggers? Aubrey Love wrote an article to help, An Introduction to SQL Triggers.
- Do you want to explore enabling temporal tables in your environment? Koen Verbeeck wrote an informative article titled, Introduction to SQL Server Temporal Tables.
- Ameena Lalani wrote a detailed article SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 1, comparing the three methods.
About the author
Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-07-27
