未加星标

Managing Temporal Table History in SQL Server 2016

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

By:Aaron Bertrand || Related Tips:More >SQL Server 2016

Problem

SQL Server 2016 introduced a new feature, Temporal Tables, which allow you to keep a historical record of all of the versions of each row in a table. As rows get introduced, changed, and deleted over time, you can always see what the table looked like during a certain time period or at a specific point in time.

You may have heard Temporal Tables referred to as system-versioned tables. What happens is that the historical versions of rows, according to the system time of their last modification, are stored in a separate table, while the current version resides in the original table.

For tables that don't change very often, this works fantastic - queries against the base table know, based on the filter criteria, whether to get the data from the base table or the history table. For tables with a high volume of insert/delete activity, however, or with rows that get updated frequently, the history table can quickly grow out of control. Imagine a table with 100 rows, and you update those 100 rows 100 times each - you now have 100 rows in the base table, and 9,900 rows in the history table (99 versions of each row).

While there are definitely going to be regulatory/auditing exceptions, in many cases, you won't want or need to keep every single version of every single row for all of time.

Solution

The MSDN article, Manage Retention of Historical Data in System-Versioned Temporal Tables , provides a few options:

Stretch Database Table Partitioning (sliding window) Custom cleanup script

The way these solutions are explained, though, lead you to make a blanket choice about retaining historical data only based on a specific point in time (say, archive everything from before January 1, 2017) or fixed windows (once a month, switch out the oldest month). This may be perfectly adequate for your requirements, and that's okay.

When I considered these solutions, I immediately envisioned a scenario that they wouldn't cover: what if I want to keep only the last three versions of a row, regardless of when those modifications took place? Or all previous versions from the past two weeks or the current calendar year, plus one additional version before that? If I archive or delete based only on a point in time, then I might keep too many versions of some rows, and no historical versions of other rows. If I want to keep the three previous versions, I can't possibly enforce that based on a point in time.

Any criteria can be accomplished, of course, if we put a little more thought into the "custom cleanup script" solution. The procedure demonstrated in the documentation accepts a specific datetime value, and deletes all historical data before that point. I'd like to demonstrate how to accomplish a selective delete (or archiving into yet another historical location) using a different set of criteria.

First, we need a base table, and a few rows:

CREATE TABLE dbo.Employees
(
EmployeeID int PRIMARY KEY,
FirstName nvarchar(64),
LastName nvarchar(64),
Salary int,
ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYESTEM_VERSIONING = OFF);
INSERT dbo.Employees(EmployeeID, FirstName, LastName, Salary)
VALUES (1, N'Bobby', N'Orr', 25000),
(2, N'Milt', N'Schmidt', 25000),
(3, N'Eddie', N'Shore', 25000);

Now, even though SYSTEM_VERSIONING is OFF, the ValidFrom and ValidTo values are populated with the time of the insert and the end of the day on 9999-12-31, respectively. If you update the data in this table right now, the ValidFrom value will update the current time, but no historical version of the row will be stored anywhere.

We can then create a history table. The columns and data types must match, but the history table can't have constraints. So we're going to create a clustered index on EmployeeID, ValidFrom, ValidTo:

CREATE TABLE dbo.Employees_History
(
EmployeeID int NOT NULL,
FirstName nvarchar(64),
LastName nvarchar(64),
Salary int,
ValidFrom datetime2(7) NOT NULL,
ValidTo datetime2(7) NOT NULL
);
CREATE CLUSTERED INDEX EmployeeID_From_To
ON dbo.Employees_History(EmployeeID, ValidFrom, ValidTo);

Next, we'll fictitiously populate it with some historical versions of these rows, just as if I had set this up a couple of years ago (this is *absolutely not* a demonstration of how Temporal Tables should work, nor a recommendation to ever do it this way; we're just trying to set up some dummy data):

-- a historical version representing when we updated salary:
INSERT dbo.Employees_History
(
EmployeeID, FirstName, LastName, Salary, ValidFrom, ValidTo
)
SELECT EmployeeID, FirstName, LastName, 20000, DATEADD(YEAR, -1, ValidFrom), ValidFrom
FROM dbo.Employees;
INSERT dbo.Employees_History
(
EmployeeID, FirstName, LastName, Salary, ValidFrom, ValidTo
)
-- then another salary update from a year before:
SELECT EmployeeID, FirstName, LastName, 15000, DATEADD(YEAR, -1, ValidFrom), ValidFrom
FROM dbo.Employees_History
-- and a row that has been "deleted" from the primary table
UNION ALL
SELECT 4, N'Phil', N'Esposito', 24500, '20150101', '20161231';
-- then, finally, let's add a new row that doesn't exist in history:
INSERT dbo.Employees(EmployeeID, FirstName, LastName, Salary)
VALUES(5, N'Brad', N'Marchand', 22750);

If we take a quick look, we have 4 rows in the base table, and 7 rows in history:

SELECT * FROM dbo.Employees;
SELECT * FROM dbo.Employees_History;

Base table and history table rows (click to enlarge)


Managing Temporal Table History in SQL Server 2016

Now, we can turn system versioning for the table ON:

ALTER TABLE dbo.Employees SET (SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.Employees_History,
DATA_CONSISTENCY_CHECK = ON
));

And just for kicks, let's update one row:

UPDATE dbo.Employees SET FirstName = N'Milton'
WHERE EmployeeID = 2;

What this does, effectively, is moves the existing row from the base table to the history table, updates the ValidTo value to the current time, then creates a new row in the base table with the updated column and a new ValidFrom value. (That is what happens logically , but not necessarily what happens physically .) Now the two tables look like this - up top, I've highlighted the changed value in the base table, and below, the row that now appears in the history table (click to enlarge):


Managing Temporal Table History in SQL Server 2016

Highlighting changes after an update (click to enlarge)

This should demonstrate the purpose of this article: As you update more rows in the base table, the history table can very quickly ramp up and take over your disk, especially if the rows are a lot wider than this simple example.

Identifying Rows to Clean up

Depending on the rules you want to use to determine which history rows to keep or throw away, it should be easy in this case to visually identify those rows, and then build the proper query.

First, let's look at the total set of rows we have in our base table and the history table together:

SELECT *, src = 'Base table' F

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerSDNRYRIMTI
分页:12
转载请注明
本文标题:Managing Temporal Table History in SQL Server 2016
本站链接:http://www.codesec.net/view/533355.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(66)