Can I create different SQL script for system version table creation and primary key in SQL Server? When I do it, its throwing an error

System version table must have primary key defined

For example:

CREATE TABLE test
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [name] varchar (1024),
    [SysStart] [datetime2] (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] [datetime2] (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = TESTHISTORY, DATA_CONSISTENCY_CHECK = ON))

ALTER TABLE Test
    ADD CONSTRAINT [PK_Test]
        PRIMARY KEY CLUSTERED (ID) ASC

CodePudding user response：

Specify the constraint in the CREATE TABLE statement, eg:

Create table test
(
    [ID] int identity(1,1) not null,
    Constraint [PK_Test] PRIMARY KEY CLUSTERED (ID),
    [name] varchar (1024),
    [SysStart] [datetime2] (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] [datetime2] (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd]),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TESTHISTORY, DATA_CONSISTENCY_CHECK = ON))

CodePudding user response：

You can.

use tempdb;
drop table if exists dbo.test;
drop table if exists dbo.TESTHISTORY;

CREATE TABLE dbo.Test
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [name] varchar (1024),
    [SysStart] [datetime2] (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] [datetime2] (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)

ALTER TABLE dbo.Test
    ADD CONSTRAINT [PK_Test]
        PRIMARY KEY CLUSTERED (ID)

ALTER TABLE dbo.Test  
    SET (SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.TESTHISTORY,
        DATA_CONSISTENCY_CHECK = ON
    )
);

It is probably my limited imagination, but I can't for the life of me think of what you stand to gain by doing so. That is, what outcome does having the primary key be a separate statement enable that doing inline inhibits?