CODEWITHSUNDEEP
sql-servertemporal-tablesalter-column
bm11
bm11

Reputation: 43

How can I change the precision of a datetime2 for a system-versioned table (temporal table) in SQL Server?

I have 62 tables that were set up as system-versioned/temporal tables except the start and end time columns were set up as datetime2(0) as shown below.

SysStartTime [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,

Because of this, we have records within the history table that appear to be duplicated because we lose the necessary precision when records were modified multiple times by our system. I haven't been able to find any documentation on modifying those two columns, but tried the following code:

ALTER TABLE SystemVersionedTable 
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE SystemVersionedTable 
    ALTER COLUMN SysStartTime DATETIME2(7);

ALTER TABLE SystemVersionedTable 
    ALTER COLUMN SysEndTime DATETIME2(7);

ALTER TABLE SystemVersionedTable 
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.dbo_SystemVersionedTable));

However, I still get the following error:

Period column 'SysStartTime' in a system-versioned temporal table cannot be altered.

Is there a way to alter those two columns and set it to datetime2(7)?

Upvotes: 0

Views: 1221

Answers (1)

DD974
DD974

Reputation: 21

The script below worked for me (of course you need to replace {tableSchema}, {tableName}, {historyTableSchema}, {historyTableName} with your values).

The script sets the new precision to 7 for SysStartTime and SysEndTime.

Note how the SysEndTime column needs to have the maximum DATETIME2 value for the given precision (hence the UPDATE).

Also note how the index ix_{historyTableName}, which is created by default when defining temporal tables, needs to be dropped and recreated.

See for reference: https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15#migrate-existing-tables-to-built-in-support

BEGIN TRANSACTION

    ALTER TABLE [{tableSchema}].[{tableName}] SET (SYSTEM_VERSIONING = OFF)
    ALTER TABLE [{tableSchema}].[{tableName}] DROP PERIOD FOR SYSTEM_TIME
    DROP INDEX [ix_{historyTableName}] ON [{historyTableSchema}].[{historyTableName}]
    GO

    ALTER TABLE [{tableSchema}].[{tableName}]               ALTER COLUMN SysStartTime DATETIME2(7) NOT NULL
    ALTER TABLE [{tableSchema}].[{tableName}]               ALTER COLUMN SysEndTime   DATETIME2(7) NOT NULL
    ALTER TABLE [{historyTableSchema}].[{historyTableName}] ALTER COLUMN SysStartTime DATETIME2(7) NOT NULL
    ALTER TABLE [{historyTableSchema}].[{historyTableName}] ALTER COLUMN SysEndTime   DATETIME2(7) NOT NULL
    CREATE CLUSTERED INDEX [ix_{historyTableName}] ON [{historyTableSchema}].[{historyTableName}] (SysEndTime, SysStartTime)
    GO

    UPDATE [{tableSchema}].[{tableName}] SET SysEndTime = CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')
    GO

    ALTER TABLE [{tableSchema}].[{tableName}] ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
    ALTER TABLE [{tableSchema}].[{tableName}] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [{historyTableSchema}].[{historyTableName}], DATA_CONSISTENCY_CHECK = ON))
    GO

COMMIT TRANSACTION

Upvotes: 2

Related Questions