What are the challenges you face when working across database platforms? Take the survey
Options

incorrect create table generated for temporal tables ?

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
Tagged:

Answers

  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    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.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.