Time Traveling with Temporal Tables on SQL Server 2016

  • Sergiy

    SSC Guru

    Points: 109884

    Another question: what's gonna happen if I change a column data type from, say, varchar(50) to nvarchar(100)?

    Or add a new column in between 2 existing ones?

  • csj

    Old Hand

    Points: 376

    It is not a problem to change datatype from length 50 to 100. But it can be a problem to change from 100 to 50 if a row has a datalengt greater than 50.

    It is not possible to add a column between 2 other columns in SQL Server. A new column will alweays be the last column in the table. SSMS can create a script, in the way that a new column can be placed between 2 other columns. This is done by creating a new table, copy data, ..., rename the tables, drop the old table, ... But this is not possible when the table is a system_version table.

    Carsten

  • Sergiy

    SSC Guru

    Points: 109884

    csj (10/21/2016)


    It is not a problem to change datatype from length 50 to 100. But it can be a problem to change from 100 to 50 if a row has a datalengt greater than 50.

    It is not possible to add a column between 2 other columns in SQL Server. A new column will alweays be the last column in the table. SSMS can create a script, in the way that a new column can be placed between 2 other columns. This is done by creating a new table, copy data, ..., rename the tables, drop the old table, ... But this is not possible when the table is a system_version table.

    Carsten

    My question was not so much about length, but about varchar - nvarchar change.

    You're right, if I do it in SSMS then the table gets dropped at some stage.

    If it's allowed then the whole history will be lost.

    If not - then any changes to table design, apart from adding/dropping the last column, are prohibited for version controlled tables.

  • csj

    Old Hand

    Points: 376

    The same rules as for other tables!!! If it is possible - and it will always be possible to alter from VARCHAR(50) to NVARCHAR(100) - then it's also allowed for system_version'ed tables.

    In SQL Server a new column will always be added as the last column in the table, if you use ALTER TABLE ... ADD COLUMN. In other SQL-based databasesystems it is possible to specify that the new column should be placed before or after another column - but not in SQL Server.

  • jay0

    Grasshopper

    Points: 18

    deleteme

  • h.tobisch

    SSCommitted

    Points: 1671

    Hi,
    what i am missing in this feature : would like to exclude  columns from versioning , e.g. "last updated" when nothing changes.

    question: what is that  WAITFOR DELAY '00:00:02'; for ?

    best regards herbert

  • Johan Åhlén

    Ten Centuries

    Points: 1154

    h.tobisch - Monday, September 4, 2017 1:57 AM

    Hi,
    what i am missing in this feature : would like to exclude  columns from versioning , e.g. "last updated" when nothing changes.

    question: what is that  WAITFOR DELAY '00:00:02'; for ?

    best regards herbert

    Hi,
    There is currently no way to exclude columns from versioning and I don't think Microsoft is planning to implement it.
    The WAITFOR DELAY '00:00:02' waits two seconds and is to make sure there is a time gap between the operations.
    Best Regards
    Johan

  • vl1969-734655

    Mr or Mrs. 500

    Points: 545

    this is funny 🙂
    some time ago, about 6 or 7 years back, I was working  on the project where we implement similar feature to version control the data. and in very similar way. 

    each table where we needed to have historical data, has 2 columns at the end of the table
    StartDate and EndDate. StartDate was filled in when record was inserted. and EndDate was filled 
    when record was deleted AKA "Soft Delete" 
    when ever we needed to change some info in the record that required history keeping, the process was like ,  copy the record -> date stamp enddate in the original record -> change the new record.
    we also had some tables where additional columns kept  the EditDate and editByUserID info. 
    so we can see who changed the record and when. 

    later on, we also had implemented the data retention feature ,again similar to temporal tables, where a trigger on the table would store any changes to data in a history table.
    as in, what record, what column(s) changed , the old value and new value and userID.

    not sure how flexible this new functionality is, but it is a good option to have.

  • N_Muller

    SSCarpal Tunnel

    Points: 4035

    This is a great article and I have always struggled with temporal data, every time needing a special case.  Unfortunately for me, I am not yet using SQL 2016 and some of its great feature. We split data into operational, short term historical and archival.  Archival data is data older than say 12 months and usually reside on a different server with very high storage capacity, yet with slower performance.  I have three questions:

    1. Does SQL 2016 has any built-in mechanism for the background process that cleans the historical temporal table to move the data to the archival database on a different server?
    2. Can the retention period be parameterized to provide elasticity and configurability to the data retention?
    3. What is the performance impact on having the historical table?  Is the time of data move from the temporal (main) table to historical table configurable? How about the time the clean up of the historical table? How often and what is its performance impact on the overall CPU and memory usage?

  • Johan Åhlén

    Ten Centuries

    Points: 1154

    N_Muller - Tuesday, September 5, 2017 7:16 AM

    This is a great article and I have always struggled with temporal data, every time needing a special case.  Unfortunately for me, I am not yet using SQL 2016 and some of its great feature. We split data into operational, short term historical and archival.  Archival data is data older than say 12 months and usually reside on a different server with very high storage capacity, yet with slower performance.  I have three questions:

    1. Does SQL 2016 has any built-in mechanism for the background process that cleans the historical temporal table to move the data to the archival database on a different server?
    2. Can the retention period be parameterized to provide elasticity and configurability to the data retention?
    3. What is the performance impact on having the historical table?  Is the time of data move from the temporal (main) table to historical table configurable? How about the time the clean up of the historical table? How often and what is its performance impact on the overall CPU and memory usage?

    Glad you enjoyed the article!

    1. No. Only Azure SQL Databases have a built-in cleaning mechanism.
    2. No.
    3. Performance is very good (from my own experience). Data always moves immediately from the main table to the historical table. Cleaning up the historical table doesn't have high performance impact if you do it right. I haven't had any problems with it.

  • Geoff A

    SSChampion

    Points: 11511

    N_Muller - Tuesday, September 5, 2017 7:16 AM

    This is a great article and I have always struggled with temporal data, every time needing a special case.  Unfortunately for me, I am not yet using SQL 2016 and some of its great feature. We split data into operational, short term historical and archival.  Archival data is data older than say 12 months and usually reside on a different server with very high storage capacity, yet with slower performance.  I have three questions:

    1. Does SQL 2016 has any built-in mechanism for the background process that cleans the historical temporal table to move the data to the archival database on a different server?
    2. Can the retention period be parameterized to provide elasticity and configurability to the data retention?
    3. What is the performance impact on having the historical table?  Is the time of data move from the temporal (main) table to historical table configurable? How about the time the clean up of the historical table? How often and what is its performance impact on the overall CPU and memory usage?

    2016 would offer Stretch DB into Azure and works perfectly for the historical tables that temporals create / use.
    in my testing, the performance impact for enabling temporal tables is minimal. 
    as far as configurations, it is basically on / off. also, the history table is not required. you can enable the ValidFrom and ValidTo columns independently of the history table.

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply