incorrect create table generated for temporal tables ?

kpakala

Hi

The auto generated create table query for temporal table via red gate SQL Server source control does not look correct. create table is being generated for 'HISTORY_TABLE' as well. Is there something that needs to be done differently for temporal tables ?

SQL Server  Version Info: Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64)   Sep  5 2017 16:12:34   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

Red Gate SQL Source Control version: 7.0.24.9343


Create Table generated via Sql Server Management Studio:
CREATE TABLE [dbo].[test](

[col1] [VARCHAR](50) NOT NULL,

[col2] [VARCHAR](50) NOT NULL,

[col3] [VARCHAR](10) NOT NULL,

[col4] [CHAR](5) NOT NULL,

[col5] [VARCHAR](50) NOT NULL,

[col6] [BIT] NULL,

[col7] [VARCHAR](50) NULL,

[col8] [BIT] NOT NULL,

[col9] [VARCHAR](50) NOT NULL,

[SYS_START_TIME] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,

[SYS_END_TIME] [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,

CONSTRAINT [PK_schedules_new] PRIMARY KEY CLUSTERED 

(

[col1] ASC,

[col2] ASC,

[col3] ASC,

[col4] ASC,

[col5] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

PERIOD FOR SYSTEM_TIME ([SYS_START_TIME], [SYS_END_TIME])

) ON [PRIMARY]

WITH

(

SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[test_history] )

)

GO

Generated using Redgate  sql source control:

 

CREATE TABLE [dbo].[test_history]

(

[col1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col4] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col6] [bit] NULL,

[col7] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[col8] [bit] NOT NULL,

[col9] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[SYS_START_TIME] [datetime2] NOT NULL,

[SYS_END_TIME] [datetime2] NOT NULL

) ON [PRIMARY]

WITH

(

DATA_COMPRESSION = PAGE

)

GO

CREATE CLUSTERED INDEX [ix_test_history] ON [dbo].[test_history] ([SYS_END_TIME], [SYS_START_TIME]) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]

GO

CREATE TABLE [dbo].[test]

(

[col1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col4] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[col6] [bit] NULL,

[col7] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[col8] [bit] NOT NULL,

[col9] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__schedules__MODIF__2F8655EF] DEFAULT ('?'),

[SYS_START_TIME] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

[SYS_END_TIME] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME (SYS_START_TIME, SYS_END_TIME),

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([dataset], [col2], [col3], [col4], [col5]) ON [PRIMARY]

) ON [PRIMARY]

WITH

(

SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[test_history])

)

GO

Sergio R

Yes, SQL Source Control scripts table differently, however this should create the temporal table and its associated history table when deploying to a live database.