Temporal table maker

This post is another in the continuing theme of "making things consistent." We were voluntold to help another team get their staging environment set up. Piece of cake, SQL Compare made it trivial to snap the tables over.

Oh, we don't want these tables in Custom schema, we want them in dbo. No problem, SQL Compare again and change owner mappings and bam, out come all the tables.

Oh, can we get this in near real-time? Say every 15 minutes. ... Transaction replication to the rescue!

Oh, we don't know what data we need yet so could you keep it all, forever? ... Temporal tables to the rescue?

Yes, temporal tables is perfect. But don't put the history table in the same schema as the table, put in this one. And put all of that in its own file group.

And that's what this script does. It

generates a table definition for an existing table, copying it into a new schema while also adding in the start/stop columns for temporal tables.

crates the clustered column store index command

creates a non-clustered index against the start/stop columns and the natural key(s)

Alters the original table to add in our start/stop columns with defaults and the period

Alters the original table to turn on versioning

How does it do all that? It finds all the tables that exist in our source schema and doesn't yet exist in the target schema. I build out a select * query against that table and feed it into sys.dm_exec_describe_first_result_set to identify the columns. And since sys.dm_exec_describe_first_result_set so nicely brings back the data type with length, precision and scale specified, we might as well use that as well. By specifying a value of 1 for browse_information_mode parameter, we will get the key columns defined for us. Which is handy when we want to make our non-clustered index.

DECLARE
    @query nvarchar(4000)
,   @targetSchema sysname = 'dbo_HISTORY'
,   @tableName sysname
,   @targetFileGroup sysname = 'History'

DECLARE
    CSR CURSOR
FAST_FORWARD
FOR
SELECT ALL
    CONCAT(
    'SELECT * FROM '
    ,   s.name
    ,   '.'
    ,   t.name) 
,   t.name
FROM 
    sys.schemas AS S
    INNER JOIN sys.tables AS T
    ON T.schema_id = S.schema_id
WHERE
    1=1
    AND S.name = 'dbo'
    AND T.name NOT IN
    (SELECT TI.name FROM sys.schemas AS SI INNER JOIN sys.tables AS TI ON TI.schema_id = SI.schema_id WHERE SI.name = @targetSchema)

;
OPEN CSR;
FETCH NEXT FROM CSR INTO @query, @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- do something
    SELECT
        CONCAT
    (
        'CREATE TABLE '
    ,   @targetSchema
    ,   '.'
    ,   @tableName
    ,   '('
    ,   STUFF
        (
            (
            SELECT
                CONCAT
                (
                    ','
                ,   DEDFRS.name
                ,   ' '
                ,   DEDFRS.system_type_name
                ,   ' '
                ,   CASE DEDFRS.is_nullable
                    WHEN 1 THEN ''
                    ELSE 'NOT '
                    END
                ,   'NULL'
                )
            FROM
                sys.dm_exec_describe_first_result_set(@query, N'', 1) AS DEDFRS
            ORDER BY
                DEDFRS.column_ordinal
            FOR XML PATH('')
            )
        ,   1
        ,   1
        ,   ''
        )
        ,   ', SysStartTime datetime2(7) NOT NULL'
        ,   ', SysEndTime datetime2(7) NOT NULL'
        ,   ')'
        ,   ' ON '
        ,   @targetFileGroup
        ,   ';'
        ,   CHAR(13)
        ,   'CREATE CLUSTERED COLUMNSTORE INDEX CCI_'
        ,   @targetSchema
        ,   '_'
        ,   @tableName
        ,   ' ON '
        ,   @targetSchema
        ,   '.'
        ,   @tableName
        ,   ' ON '
        ,   @targetFileGroup
        ,   ';'
        ,   CHAR(13)
        ,   'CREATE NONCLUSTERED INDEX IX_'
        ,   @targetSchema
        ,   '_'
        ,   @tableName
        ,   '_PERIOD_COLUMNS '
        ,   ' ON '
        ,   @targetSchema
        ,   '.'
        ,   @tableName

        ,   '('
        ,   'SysEndTime'
        ,   ',SysStartTime'
        ,   (
                SELECT
                    CONCAT
                    (
                        ','
                    ,   DEDFRS.name
                    )
                FROM
                    sys.dm_exec_describe_first_result_set(@query, N'', 1) AS DEDFRS
                WHERE
                    DEDFRS.is_part_of_unique_key = 1
                ORDER BY
                    DEDFRS.column_ordinal
                FOR XML PATH('')
                )
        ,   ')'
        ,   ' ON '
        ,   @targetFileGroup
        ,   ';'
        ,   CHAR(13)
        ,   'ALTER TABLE '
        ,   'dbo'
        ,   '.'
        ,   @tableName
        ,   ' ADD '
        ,   'SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN'
        ,   ' CONSTRAINT DF_'
        ,   'dbo_'
        ,   @tableName
        ,   '_SysStartTime DEFAULT SYSUTCDATETIME()'
        ,   ', SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN'
        ,   ' CONSTRAINT DF_'
        ,   'dbo_'
        ,   @tableName
        ,   '_SysEndTime DEFAULT DATETIME2FROMPARTS(9999, 12, 31, 23,59, 59,9999999,7)'
        ,   ', PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);'
        ,   CHAR(13)
        ,   'ALTER TABLE '
        ,   'dbo'
        ,   '.'
        ,   @tableName
        ,   ' SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = '
        ,   @targetSchema
        ,   '.'
        ,   @tableName
        ,   '));'

    )

FETCH NEXT FROM CSR INTO @query, @tableName;
END
CLOSE CSR;
DEALLOCATE CSR;

Lessons learned

The exampled I cobbled together from MSDN were great, until they weren't. Be wary of anyone who doesn't specify lengths - one example used datetime2 for the start/stop columns, the other specified datetime2(0). The default precision with datetime2 is 7, which is very much not 0. Those data types differences were incompatible for temporal table and history.

Cleaning up from that mess was ugly. I couldn't drop the start/stop columns until I dropped the PERIOD column. One doesn't drop a PERIOD though, one has to DROP PERIOD FOR SYSTEM_TIME

I prefer to use the *FromParts methods where I can so that's in my default instead of casting strings. Out ambiguity of internationalization!

This doesn't account for tables with bad names and potentially without primary/unique keys defined. My domain was clean so beware of this a general purpose temporal table maker.

Improvements

How can you make this better? My hard coded dbo should have been abstracted out to a @sourceSchema variable. I should have used QUOTENAME for all my entity names. I could have stuffed all those commands into either a table or invoked it directly with a sp_execute_sql call. I should have abused CONCAT more Wait, that's done. That's very well done.

Finally, you are responsible for the results of this script. Don't run it anywhere without evaluating and understanding the consequences.