7

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.

8

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.

  • 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. – Anthony Genovese Oct 3 '17 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.