February 14, 2018 at 10:43 pm
Comments posted to this topic are about the item Creation of a temporal table
February 14, 2018 at 10:44 pm
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”
February 15, 2018 at 6:42 am
Thanks, Evgeny! It is nice to get a question that forces me to dig into one of the newest features.
February 15, 2018 at 7:22 am
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/
February 15, 2018 at 7:52 am
Stewart "Arturius" Campbell - Wednesday, February 14, 2018 10:44 PMTemporal 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.
😀
February 15, 2018 at 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.
February 15, 2018 at 9:26 am
George Vobr - Thursday, February 15, 2018 8:33 AMGreat 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.
February 15, 2018 at 9:43 am
Luis Cazares - Thursday, February 15, 2018 9:26 AMGeorge Vobr - Thursday, February 15, 2018 8:33 AMGreat 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.
(
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/
February 15, 2018 at 10:37 am
Sean Lange - Thursday, February 15, 2018 9:43 AMLuis Cazares - Thursday, February 15, 2018 9:26 AMGeorge Vobr - Thursday, February 15, 2018 8:33 AMGreat 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))
;
February 15, 2018 at 11:03 am
Luis Cazares - Thursday, February 15, 2018 10:37 AMSean Lange - Thursday, February 15, 2018 9:43 AMLuis Cazares - Thursday, February 15, 2018 9:26 AMGeorge Vobr - Thursday, February 15, 2018 8:33 AMGreat 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/
February 15, 2018 at 7:03 pm
Luis Cazares - Thursday, February 15, 2018 9:26 AMGeorge Vobr - Thursday, February 15, 2018 8:33 AMGreat 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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply