Creation of a temporal table

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    Comments posted to this topic are about the item Creation of a temporal table

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Nice question, thanks Evgeny
    Temporal tables are really cool... use them extensively in my current contract

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks, Evgeny! It is nice to get a question that forces me to dig into one of the newest features.

  • Sean Lange

    SSC Guru

    Points: 286536

    Great question. Forced me to look deeper into this which I had not done previously. I can see using this feature right away....well as soon as I can convince management to let us upgrade to 2016.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    Stewart "Arturius" Campbell - Wednesday, February 14, 2018 10:44 PM

    Temporal tables are really cool... use them extensively in my current contract

    I agree.  We've been using them for several months.  I'm a huge fan.
    😀

  • George Vobr

    SSCrazy Eights

    Points: 9248

    Great question, thanks Evgeny. But I'm not sure if parameter SYSTEM_VERSIONING = ON must be specified.
    How does it mean in Docs when Creating and System-Versioned Temporal Table states:

    Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation,
    especially in prototypes and test environments. It is also the simplest way to create a temporal table since
    it doesn’t require any parameter in SYSTEM_VERSIONING clause...


    I don't have the version of SQL Server 2016 so I can't try it in practice.

  • Luis Cazares

    SSC Guru

    Points: 183637

    George Vobr - Thursday, February 15, 2018 8:33 AM

    Great question, thanks Evgeny. But I'm not sure if parameter SYSTEM_VERSIONING = ON must be specified.
    How does it mean in Docs when Creating and System-Versioned Temporal Table states:

    Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation,
    especially in prototypes and test environments. It is also the simplest way to create a temporal table since
    it doesn’t require any parameter in SYSTEM_VERSIONING clause...


    I don't have the version of SQL Server 2016 so I can't try it in practice.

    ON is not a parameter on SYSTEM_VERSIONING. The parameter would be HISTORY_TABLE, DATA_CONSISTENCY_CHECK and maybe others that I'm not aware of.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Sean Lange

    SSC Guru

    Points: 286536

    Luis Cazares - Thursday, February 15, 2018 9:26 AM

    George Vobr - Thursday, February 15, 2018 8:33 AM

    Great question, thanks Evgeny. But I'm not sure if parameter SYSTEM_VERSIONING = ON must be specified.
    How does it mean in Docs when Creating and System-Versioned Temporal Table states:

    Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation,
    especially in prototypes and test environments. It is also the simplest way to create a temporal table since
    it doesn’t require any parameter in SYSTEM_VERSIONING clause...


    I don't have the version of SQL Server 2016 so I can't try it in practice.

    ON is not a parameter on SYSTEM_VERSIONING. The parameter would be HISTORY_TABLE, DATA_CONSISTENCY_CHECK and maybe others that I'm not aware of.

    According to the document it is. From the link above you would use this for an anonymous history table.


    CREATE TABLE Department 

      DeptID int NOT NULL PRIMARY KEY CLUSTERED
     , DeptName varchar(50) NOT NULL
     , ManagerID INT NULL
     , ParentDeptID int NULL
     , 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) 
    ;

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Luis Cazares

    SSC Guru

    Points: 183637

    Sean Lange - Thursday, February 15, 2018 9:43 AM

    Luis Cazares - Thursday, February 15, 2018 9:26 AM

    George Vobr - Thursday, February 15, 2018 8:33 AM

    Great question, thanks Evgeny. But I'm not sure if parameter SYSTEM_VERSIONING = ON must be specified.
    How does it mean in Docs when Creating and System-Versioned Temporal Table states:

    Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation,
    especially in prototypes and test environments. It is also the simplest way to create a temporal table since
    it doesn’t require any parameter in SYSTEM_VERSIONING clause...


    I don't have the version of SQL Server 2016 so I can't try it in practice.

    ON is not a parameter on SYSTEM_VERSIONING. The parameter would be HISTORY_TABLE, DATA_CONSISTENCY_CHECK and maybe others that I'm not aware of.

    According to the document it is. From the link above you would use this for an anonymous history table.

    I'm struggling with the wording, but there's a difference which is not completely clear. ON is the value for SYSTEM_VERSIONING which is an option of CREATE TABLE. HISTORY _TABLE and DATA_CONSISTENCY_CHECK are the parameters that can be defined for SYSTEM_VERSIONING (with their corresponding values).

    CREATE TABLE Department 

      DeptID int NOT NULL PRIMARY KEY CLUSTERED
     , DeptName varchar(50) NOT NULL
     , ManagerID INT NULL
     , ParentDeptID int NULL
     , 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 (HISTORY_TABLE = dbo.DepartmentHistory)) 
    ;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Sean Lange

    SSC Guru

    Points: 286536

    Luis Cazares - Thursday, February 15, 2018 10:37 AM

    Sean Lange - Thursday, February 15, 2018 9:43 AM

    Luis Cazares - Thursday, February 15, 2018 9:26 AM

    George Vobr - Thursday, February 15, 2018 8:33 AM

    Great question, thanks Evgeny. But I'm not sure if parameter SYSTEM_VERSIONING = ON must be specified.
    How does it mean in Docs when Creating and System-Versioned Temporal Table states:

    Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation,
    especially in prototypes and test environments. It is also the simplest way to create a temporal table since
    it doesn’t require any parameter in SYSTEM_VERSIONING clause...


    I don't have the version of SQL Server 2016 so I can't try it in practice.

    ON is not a parameter on SYSTEM_VERSIONING. The parameter would be HISTORY_TABLE, DATA_CONSISTENCY_CHECK and maybe others that I'm not aware of.

    According to the document it is. From the link above you would use this for an anonymous history table.

    I'm struggling with the wording, but there's a difference which is not completely clear. ON is the value for SYSTEM_VERSIONING which is an option of CREATE TABLE. HISTORY _TABLE and DATA_CONSISTENCY_CHECK are the parameters that can be defined for SYSTEM_VERSIONING (with their corresponding values).

    CREATE TABLE Department 

      DeptID int NOT NULL PRIMARY KEY CLUSTERED
     , DeptName varchar(50) NOT NULL
     , ManagerID INT NULL
     , ParentDeptID int NULL
     , 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 (HISTORY_TABLE = dbo.DepartmentHistory)) 
    ;

    Agreed the wording, and the syntax, of this is a challenge.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • George Vobr

    SSCrazy Eights

    Points: 9248

    Luis Cazares - Thursday, February 15, 2018 9:26 AM

    George Vobr - Thursday, February 15, 2018 8:33 AM

    Great question, thanks Evgeny. But I'm not sure if parameter SYSTEM_VERSIONING = ON must be specified.
    How does it mean in Docs when Creating a System-Versioned Temporal Table states:

    Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation,
    especially in prototypes and test environments. It is also the simplest way to create a temporal table since
    it doesn’t require any parameter in SYSTEM_VERSIONING clause...


    I don't have the version of SQL Server 2016 so I can't try it in practice.

    ON is not a parameter on SYSTEM_VERSIONING. The parameter would be HISTORY_TABLE, DATA_CONSISTENCY_CHECK and maybe others that I'm not aware of.

    Thanks for your post. I've read the documentation very quickly..:blush:. Finally, I have found the time to study more thoroughly the CREATE TABLE docs and the syntax SYSTEM_VERSIONING clause is:

    SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]

    It is clear from the syntax that this clause must be specified for the creation a system-versioned temporal table.

Viewing 11 posts - 1 through 11 (of 11 total)

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