Temporal Table : specific column changed version details only

Question

Need help/ideas on querying temporal table. I have SQL versioning enabled on table. This table have currently 15 columns.

The exact requirement is to identify how many times a "OrderStatus" column gets updated and who updated it at what time? We are only looking to see how many times "OrderStatus" column get updated between a specific date with all the other columns selected by default.

Answer 1

I just did something like this using CTE's In my case I'm looking for when a task moves from one queue to another queue and I need to capture the date that happened on a particular reporting date.

So in your case you could do something similar.

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

Answer 2

The problem with what you are trying to do is that the history table does not say which columns have been updated.

What we need to do then, is first query all rowversions between those dates, and then use LAG/LEAD to check if the row has changed.

Problem: if we ask for the given dates, we do not get the version before that. We need to query the table again for that. Using BETWEEN (inclusive) instead of FROM (exclusive) on the main query makes this more difficult, as we then have to find a way to get the row < @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
    ))

---

Change Data Capture

With CDC, it's somewhat more complex to set the query up, but I would imagine it would perform better.

You would typically use fn_cdc_get_column_ordinal to get the column number, then use that in the WHERE to filter against the update mask.

You also need to pass in the most recent received @lsn as binary(10) (or empty binary(10) if starting from fresh). You receive the new one back in the first resultset below.

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;

Answer 3

Your question is lacking in some details, so I've made an attempt based on a number of assumptions.

[re-]Create Representative Table

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
  )
);

Create Sample Data

Note the use of WAITFOR is to provide some more illustrative gaps between events.

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
;

Raw Data

Let's take a peek at the raw data

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

OrderId	OrderStatus	UpdatedBy	ValidFrom	ValidTo
9	NEW	George	2021-02-17 10:27:35.1632525	2021-02-17 10:27:37.1719903
9	IN PROGRESS	George	2021-02-17 10:27:37.1719903	2021-02-17 10:27:39.1852032
9	DESPATCHED	George	2021-02-17 10:27:39.1852032	9999-12-31 23:59:59.9999999
46	NEW	George	2021-02-17 10:27:35.1632525	2021-02-17 10:27:39.1852032
46	DESPATCHED	George	2021-02-17 10:27:39.1852032	2021-02-17 10:27:41.1995704
46	IN PROGRESS	George	2021-02-17 10:27:41.1995704	2021-02-17 10:27:43.2171042
46	IN PROGRESS	Not George	2021-02-17 10:27:43.2171042	2021-02-17 10:27:45.2328908
46	DESPATCHED	Still Not George	2021-02-17 10:27:45.2328908	9999-12-31 23:59:59.9999999

Query Time

; WITH _orders AS (
  SELECT OrderId
       , OrderStatus
       , UpdatedBy
       , ValidFrom
       , ValidTo
       , Lead(OrderStatus) OVER (PARTITION BY OrderId ORDER BY ValidFrom) AS NextOrderStatus
  FROM   dbo.orders FOR SYSTEM_TIME ALL
)
SELECT OrderId
     , OrderStatus
     , UpdatedBy
     , ValidFrom
FROM   _orders
WHERE  OrderStatus <> NextOrderStatus -- Only return records where the order status has changed
OR     NextOrderStatus IS NULL -- Include the "most recent" record in the results, always.
ORDER
    BY OrderId
     , ValidFrom
;

Results

OrderId	OrderStatus	UpdatedBy	ValidFrom
9	NEW	George	2021-02-17 10:27:35.1632525
9	IN PROGRESS	George	2021-02-17 10:27:37.1719903
9	DESPATCHED	George	2021-02-17 10:27:39.1852032
46	NEW	George	2021-02-17 10:27:35.1632525
46	DESPATCHED	George	2021-02-17 10:27:39.1852032
46	IN PROGRESS	Not George	2021-02-17 10:27:43.2171042
46	DESPATCHED	Still Not George	2021-02-17 10:27:45.2328908

Answer 4

Versionning cannot give you this information because in the case of "update for same value" a versionned row will be create but you will never be able to see if it is this column that have been modified or one other.

The only way to do that is to create a trigger in the table and count in some object.

In this cas I use one of the 10 user configurable counter of the profiler... As an example, the code of the trigger :

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

You can read it at any time with :

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