Search code examples
sql-servertemporal

Capture SysEndTime on SQL Server temporal table delete

is it possible to capture the SysEndTime temporal timestamp with an OUTPUT keyword?

For example:

DECLARE @MyTableVar TABLE (sysendtime datetime2);
DELETE
FROM dbo.someTable
OUTPUT DELETED.sysendtime INTO @MyTableVar 
WHERE
    colA = 'something';
SELECT sysendtime FROM @MyTableVar;

The last SELECT is returning 9999-12-31 23:59:59.000 which is the value before the DELETE exec'd.

I read that SysEndTime is set at the end of the transaction so it wouldn't be visible until all the calls in that blocked completed but that'd mean I'd have to do a secondary query on the someTable table using the "for system_time all" syntax and retrieving the max(SysEndTime) column to capture the most recent change and with that I wouldn't be guaranteed that it's the delete that as an UPDATE would set SysEndTime too.

Solution

  • No this is not possible through the OUTPUT clause because the value does not exist in a column in the system versioned table.

    SQL Server uses the transaction start time - not end time so logically it shouldn't be inherently impossible. The execution plan calls systrandatetime() in the compute scalar to get the value to use when inserting to the history table.

    enter image description here

    This is an internal function though and not exposed to us. The following query run in the same transaction should likely return something close but this is datetime datatype so will get the legacy rounding behaviour to 1/300 of a seconds. I doubt that you will find any guarantees that this even uses the exact same underlying data source either.

    SELECT transaction_begin_time
FROM   sys.dm_tran_active_transactions
WHERE  transaction_id = (SELECT transaction_id
                         FROM   sys.dm_tran_current_transaction);

    I would not suggest using the transaction DMVs directly. However as the row already exists in the history table when the DELETE statement finishes you can (inside the same transaction) just query the history table to get the highest sysendtime for the primary key of one of the deleted rows (the PK can be captured with OUTPUT if needed).

    e.g. Setup

    CREATE TABLE dbo.someTable
(
PK   INT PRIMARY KEY,
ColA VARCHAR(15),
[ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START ,
[ValidTo] datetime2 (2) GENERATED ALWAYS   AS ROW END ,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.someTableHistory))


INSERT INTO dbo.someTable
            (PK,
             ColA)
VALUES      (1,'something'),
            (2,'something');

    Then you could achieve this as below

    DECLARE @DeletedPks TABLE
(
   PK INT 
)

BEGIN TRAN

DELETE FROM dbo.someTable
OUTPUT DELETED.PK
INTO @DeletedPks
WHERE  colA = 'something';

SELECT MAX(ValidTo)
FROM   dbo.someTableHistory
WHERE  PK = (SELECT TOP 1 PK /*Doesn't matter which PK we choose*/
             FROM   @DeletedPks)

COMMIT