9

Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema:

create table blah (
    foo int identity(1,1),
    baa int
)

and I were to run the following statement:

update blah set baa = baa

would it log changes as if I'd actually updated data?

I've had a look around the MSDN article for temporary tables and the one for Modifying Data but I can't find anything there specifying this.

0
10

Converting my comment to answer ..

If the value is actually updated, then it will log changes. If you update the same value, new versions for every row will be recorded, but the column values will remain the same.

Below is the working example :

CREATE TABLE dbo.blah1
    -- PK is a requirement !!
    (
    foo INT identity(1, 1) PRIMARY KEY
    ,baa INT
    ,SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    ,SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    ,PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
    )
    WITH (SYSTEM_VERSIONING = ON);

-- insert data
insert into dbo.blah (baa)
values (1)

Now check for the data

-- check data
    SELECT * FROM  dbo.blah;
    select * from [dbo].[MSSQL_TemporalHistoryFor_629577281]

enter image description here

perform dummy update

update blah 
set baa = baa

enter image description here

Perform onemore dummy update .. there will be rows with same values but different systime - start and end.

enter image description here

You have to have a proper mechanism to clean up history table as that can grow significantly larger if you forget to trim it.

2
  • 1
    As to clean up - you think a dynamic query across all tables would be good? Even then wouldn't you want to change the main table so the start date goes back to the earliest time? And then you would want to remove those records in the history table. Oct 3 '17 at 20:00
  • This behaviour is surprising because it seems so ill-advised and means that poorly-written procedures and DML will spam a history table with redundant data. The temporal tables feature in SQL Server just seems very half-baked and inconsistent, and as of 2021 there's still only support for system_versioned tables and not application-versioned or bitemporal tables - or suspending history insertions on a per-DML-statement basis.
    – Dai
    Aug 23 at 20:50

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .