使用 查询系统版本控制的时态表FOR system_time ALL
似乎会忽略开始时间和结束时间相等的行。
例如:
/* Create a simple system-versioned table */
CREATE TABLE [dbo].[svt_test](
[string] [VARCHAR](30) NOT NULL,
[validFrom] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[validTo] [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_svt_test] PRIMARY KEY CLUSTERED
(
[string] ASC
),
PERIOD FOR SYSTEM_TIME ([validFrom], [validTo])
)
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[svt_test_history] )
);
/*
Insert and immediately delete a row.
May have to try multiple times to get a row with the same start and end
timestamp.
*/
INSERT INTO dbo.svt_test (string) VALUES ('string');
DELETE dbo.svt_test WHERE string = 'string';
/* 0 rows */
SELECT * FROM dbo.svt_test;
/* 1 row */
SELECT * FROM dbo.svt_test_history;
/*
0 rows.
Should be the "union of rows that belong to the current and the history table"
per https://msdn.microsoft.com/en-us/library/dn935015.aspx
*/
SELECT * FROM dbo.svt_test FOR system_time ALL;
/*
CONTAINED IN does not find the row either, even though MSDN says the
interval is defined as SysStartTime >= start_date_time AND SysEndTime <=
end_date_time
*/
DECLARE @Start DATETIME2(7);
SELECT @Start = validFrom FROM [dbo].[svt_test_history] WHERE string = 'string';
DECLARE @End DATETIME2(7);
SELECT @End = validTo FROM [dbo].[svt_test_history] WHERE string = 'string';
SELECT * FROM dbo.svt_test FOR system_time CONTAINED IN (@Start, @End);
/* Clean up */
ALTER TABLE dbo.svt_test SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.svt_test;
DROP TABLE dbo.svt_test_history;
我已经在 2016 SP1 CU1 (12.0.4416) 和 2016 CU3 (13.0.2186) 上对此进行了验证。我用datetime2(0)
而不是得到了相同的结果datetime2(7)
。
有人会碰巧知道这可能是错误还是设计使然?或者,也许我误解了什么?
我无法重现您所看到的。可能是因为我的电脑很慢。但另一方面,似乎 SQL Server 开发人员在构建它时考虑的是我的慢速计算机,而不是你的快速计算机。
如果我将插入和删除包装在一个事务中,它们每次都会获得相同的开始和结束时间戳。
来自时态表
因此,如果开始时间戳和结束时间戳相同,他们就会认为它发生在同一笔交易中。