A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, October 12, 2017

Temporal table maker

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.

  • 1 comment:

    Kenneth Fisher said...

    Just an FYI because the permissions are not what you would expect. In order to make a table a temporal table you have to have CONTROL on the table in question. db_ddladmin won't do it. However CONTROL on the schema will.