时态表:特定列仅更改了版本详细信息

Question

需要有关查询时态表的帮助/想法。我在表上启用了SQL版本控制。该表目前有15列。

确切的要求是识别一个"；订单状态"；列得到更新，是谁在什么时候更新的？我们只是想看看有多少次；订单状态"；列在特定日期之间更新，默认情况下选择所有其他列。

Answer 1

您的问题缺乏一些细节，因此我根据一些假设进行了尝试。

[re-]创建代表表

IF Object_ID('dbo.orders', 'U') IS NOT NULL
BEGIN
ALTER TABLE dbo.orders SET (SYSTEM_VERSIONING = OFF);
END
;
DROP TABLE IF EXISTS dbo.orders_history;
DROP TABLE IF EXISTS dbo.orders;
CREATE TABLE dbo.orders (
OrderId     int         NOT NULL IDENTITY(9,37)
, OrderStatus varchar(20) NOT NULL
, UpdatedBy   varchar(20) NOT NULL
, ValidFrom   datetime2 GENERATED ALWAYS AS ROW START
, ValidTo     datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
, CONSTRAINT pk_dbo_orders PRIMARY KEY (OrderId)
)
WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = dbo.orders_history
)
);

创建示例数据

注意，WAITFOR的使用是为了在事件之间提供一些更具说明性的间隙。

INSERT INTO dbo.orders (OrderStatus, UpdatedBy)
VALUES ('NEW', 'George')
, ('NEW', 'George')
;
WAITFOR DELAY '00:00:02';
UPDATE dbo.orders
SET    OrderStatus = 'IN PROGRESS'
WHERE  OrderId = 9
;
WAITFOR DELAY '00:00:02';
-- Mark both orders as despatched
UPDATE dbo.orders
SET    OrderStatus = 'DESPATCHED'
;
WAITFOR DELAY '00:00:02';
-- Whoops, order #46 wasn't supposed to be marked as dispatched
UPDATE dbo.orders
SET    OrderStatus = 'IN PROGRESS'
WHERE  OrderId = 46
;
WAITFOR DELAY '00:00:02';
-- Mark it as in progress again, but changing the person who did the operation
UPDATE dbo.orders
SET    OrderStatus = 'IN PROGRESS'
, UpdatedBy   = 'Not George'
WHERE  OrderId = 46
;
WAITFOR DELAY '00:00:02';
-- _Now_ it is despatched
UPDATE dbo.orders
SET    OrderStatus = 'DESPATCHED'
, UpdatedBy   = 'George'
WHERE  OrderId = 46
;

原始数据

让我们来看看原始数据

SELECT OrderId
, OrderStatus
, UpdatedBy
, ValidFrom
, ValidTo
FROM   dbo.orders FOR SYSTEM_TIME ALL
ORDER
BY OrderId
, ValidFrom
;

021-02-17 10:27:39.1852032>[/tr>021-02-17 10:27:41.1995704/table>

OrderId
9		9正在进行中		9	取消签名	乔治	9999-12-31 23:59:59.9999999
46		46	取消签名	乔治
46正在进行中		46正在进行中		46	取消签名	仍然不是乔治

版本控制无法为您提供此信息，因为在"；更新相同值"；将创建一个版本化的行，但您永远无法查看是该列被修改还是另一列被修改。

唯一的方法是在表中创建一个触发器并在某个对象中计数。

在这个案例中，我使用了探查器的10个用户可配置计数器中的一个。。。例如，触发器的代码：

CREATE TRIGGER E_U_MY_TABLE
ON dbo.MY_TABLE
FOR UPDATE
AS
SET NOCOUNT 1;
IF NOT UPDATE(OrderStatus)
RETURN;
DECLARE @COUNT BIGINT = (SELECT cntr_value 
FROM   sys.dm_os_performance_counters 
WHERE instance_name = 'User counter 1') + 1;
EXEC sp_user_counter1 @COUNT;
GO

你可以在任何时候阅读：

SELECT cntr_value 
FROM   sys.dm_os_performance_counters 
WHERE instance_name = 'User counter 1'

您尝试执行的操作的问题是，历史记录表没有说明哪些列已经更新

然后，我们需要做的是，首先查询这些日期之间的所有行版本，然后使用LAG/LEAD检查行是否已更改。

问题：如果我们询问给定的日期，我们不会得到之前的版本。我们需要再次查询该表。在主查询中使用BETWEEN(包含)而不是FROM(排除)会使这变得更加困难，因为我们必须找到一种方法来获得行< @start

SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY pkID ORDER BY SysStartTime) rn,   -- or whatever your startTime column is called
LAG(OrderStatus) OVER (PARTITION BY pkID ORDER BY SysStartTime) PrevStatus
FROM myTable t
FOR SYSTEM_TIME FROM @start TO @end    -- FROM is strictly exclusive
) t
WHERE PrevStatus <> OrderStatus OR
(rn = 1 AND EXISTS (SELECT 1
FROM myTable t2
FOR SYSTEM_TIME AT @start
WHERE t2.ID = t.ID
AND t2.OrderStatus <> t.OrderStatus
))

---

变更数据捕获

对于CDC，设置查询会更加复杂，但我认为它会表现得更好。

您通常会使用fn_cdc_get_column_ordinal来获取列编号，然后在WHERE中使用它来根据更新掩码进行筛选。

您还需要将最近收到的@lsn作为binary(10)传入(如果从新鲜开始，则为空binary(10))。您将在下面的第一个结果集中收到新的结果集。

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('myTable');  -- get the new low mark
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn();  --get the new high mark
SELECT @to_lsn;    -- send back the new high, which becomes the low on the next run
SET @lsn = sys.fn_cdc_increment_lsn (@lsn);   -- get next LSN after the old high
IF (sys.fn_cdc_get_min_lsn (N'myTable') > @lsn)
SELECT * FROM myTable;       -- need to do a full refresh
ELSE
BEGIN
DECLARE @ordinal int = sys.fn_cdc_get_column_ordinal (N'myTable', N'myCol');
SELECT *
FROM sys.fn_cdc_get_all_changes_capture_myTable
(@from_lsn, @to_lsn, 'all')
WHERE sys.fn_cdc_is_bit_set (__$update_mask, @ordinal) = 1;
END;

我刚刚使用CTE做了这样的事情。在我的情况下，我正在寻找任务何时从一个队列移动到另一个队列，并且我需要捕获特定报告日期发生的日期。

所以在你的情况下，你可以做一些类似的事情。

DECLARE @TaskType SMALLINT = 22
DECLARE @ActiveTasksIds TABLE (TaskId SMALLINT NOT NULL)
INSERT INTO @ActiveTasksIds
(
TaskId
)
SELECT T.TaskId FROM TASK.Task AS T 
INNER JOIN TASK.TaskStatus AS TS ON TS.TaskStatusId = T.TaskStatusId
WHERE T.TaskTypeId = @TaskType
AND TS.IsFinal = 0
;WITH ActiveTaskHistory AS 
(
SELECT 
ROW_NUMBER() OVER(PARTITION BY T.TaskId ORDER BY T.ValidFrom ASC) AS RowNumber
, T.*
FROM TASK.Task FOR SYSTEM_TIME ALL AS T
INNER JOIN @ActiveTasksIds AS ATI ON T.TaskId = ATI.TaskId
WHERE T.TaskTypeId = @TaskType
)
, ATH_Before AS
(
SELECT * FROM ActiveTaskHistory ATH WHERE ATH.TaskQueueId IN (5, 10)
)
, ATH_AFTER AS 
(
SELECT * FROM ActiveTaskHistory ATH WHERE ATH.TaskQueueId = 28
)
SELECT ATH_Before.TaskId
, ATH_Before.ValidFrom AS AHS_ValidFrom, ATH_EOHHS.ValidFrom AS EOHHS_ValidFrom
FROM ATH_Before
INNER JOIN ATH_AFTER ON ATH_Before.TaskId = ATH_AFTER.TaskId
AND ATH_Before.RowNumber + 1 = ATH_AFTER.RowNumber
WHERE 
CAST(ATH_AFTER.ValidFrom AS DATE) = @ReportDate