I'm trying to create Table with system versioning using Database Project.
Following schema gives error:
SQL70633: System-versioned temporal table must have history table name explicitly provided.
CREATE TABLE [dbo].[Products] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(255) NOT NULL, [ModifiedBy] NVARCHAR(127) NULL ) WITH (SYSTEM_VERSIONING = ON) GO
With explicit name:
SQL71501: Table: [dbo].[Products] has an unresolved reference to Table [history].[ProductsHistory].
SQL46010: Incorrect syntax near ].CREATE TABLE [dbo].[Products] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(255) NOT NULL, [ModifiedBy] NVARCHAR(127) NULL ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [history].ProductsHistory)) GO
I've tried both, latest version of Visual Studio 2019 (16.7.5) and latest preview (16.8.0 Preview 3.2).
The syntax in both cases is invalid. Executing the first query in SSMS returns:
The command needs a
PERIOD FOR SYSTEM_TIME
clause specifying the columns used to specify the validity period of a record.The documentation examples show how to create a temporal table with a default, automatically named history table :
In this case, the
SysStartTime
andSysEndTime
are used to specify the validity period of a record.Similar syntax is needed to create a temporal table with a user-specified table name
It's possible to create the history table on a different schema, eg
history
, as long as that schema exists, BUT it's probably not a good idea unless this solves some specific problem. The current and history table represent the same entity, depend on each other and have specific security restrictions so storing them in separate schemas can make life harder.To create the table in a separate schema, first create the schema :
Then use the schema in the table definition: