Секционирование. Для чего и как?

Set nocount, xact_abort on;
 
DECLARE @sql NVARCHAR(max) = N'',  @filial_id smallint
 
-- Создаем новые таблицы импорта
SET @sql = (
        SELECT N'Create table [tmp].' + Quotename(t2.NAME) 
        + N'(' 
        + Stuff((
                    SELECT N',' + Quotename(b.[name]) + N' ' + t.[name] 
                        + CASE 
                            WHEN t.[name] IN (N'numeric', N'decimal')
                                THEN CONCAT (N'(', b.[precision], N',', b.[scale], N')')
                            WHEN t.[name] IN (N'datetime2')
                                THEN CONCAT(N'(', b.[scale], N')')
                            WHEN t.[name] IN (N'varchar', 'char')
                                THEN CONCAT (N'(', iif(b.max_length = - 1, N'max', cast(b.max_length AS NVARCHAR(10))), N')') + IsNull(N' collate ' + b.collation_name, N'')
                            WHEN t.[name] IN (N'nvarchar', N'nchar')
                                THEN CONCAT (N'(', iif(b.max_length = - 1, N'max', cast(b.max_length / 2 AS NVARCHAR(10))), N')') + IsNull(N' collate ' + b.collation_name, N'')
                            WHEN t.[name] IN (N'varbinary', N'binary')
                                THEN CONCAT (N'(', iif(b.max_length = - 1, N'max', cast(b.max_length AS NVARCHAR(10))), N')')
                            ELSE N''
                        END 
                        + N' ' + iif(b.is_sparse = 0, N'', N'SPARSE') + N' ' + iif(b.is_nullable = 0, N'NOT NULL', N'NULL')
                    FROM sys.tables a
                    INNER JOIN sys.columns b ON a.[object_id] = b.[object_id]
                    INNER JOIN sys.types t ON b.system_type_id = t.system_type_id and b.user_type_id = t.user_type_id
                        AND b.user_type_id = t.user_type_id
                    WHERE a.object_id = aa.object_id
                    ORDER BY b.column_id ASC
                    FOR XML path(N'')
                        ,type
                    ).value(N'.', N'NVARCHAR(MAX)'), 1, 1, '') 
                    + N') ON [f' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + '];'
        FROM 
        sys.tables aa (nolock)
        inner join [load].[tables_to_load] t on aa.[name] = t.[name] and aa.schema_id = schema_id('dbo')
        Cross apply 
            (
            VALUES (aa.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N'_in')
                  ,(aa.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N'_out')
            ) t2(NAME)
        FOR XML path(N''), Type
        ).value(N'.', N'NVARCHAR(MAX)');
 
Exec (@sql);
 
-- Создать КЛАСТЕРНЫЕ индексы в таблицах для переключения
 
SET @sql = (
        SELECT CASE si.index_id
                WHEN 0
                    THEN N'/* No create statement (Heap) */'
                ELSE CASE si.is_primary_key
                        WHEN 1
                            THEN N'ALTER TABLE ' + QUOTENAME(N'tmp') + N'.' + QUOTENAME(t2.NAME) + N' ADD CONSTRAINT ' + QUOTENAME(N'PK_' + t2.NAME) + N' PRIMARY KEY ' + iif(si.index_id > 1, N'NON CLUSTERED ', N'CLUSTERED ')
                        ELSE N'CREATE ' + CASE 
                                WHEN si.[type] = 5 THEN N'CLUSTERED COLUMNSTORE '
                                WHEN si.[type] = 6 THEN N'NONCLUSTERED COLUMNSTORE '
                                ELSE iif(si.is_unique = 1, N'UNIQUE ', N'') + iif(si.index_id > 1, N'NONCLUSTERED ', N'CLUSTERED ')
                                END 
                        + N'INDEX ' + QUOTENAME(si.NAME) + N' ON ' + QUOTENAME(N'tmp') + N'.' + QUOTENAME(t2.NAME) + N' '
                        END
                    /* key def */
                    + isNull(N'(' + keys.key_definition + N')', '')
                    /* includes */
                    + CASE 
                        WHEN includes.include_definition IS NOT NULL AND si.[type] = 6 THEN N' (' + includes.include_definition + N') '
                        WHEN includes.include_definition IS NOT NULL AND not (si.[type] in (5,6)) THEN N' INCLUDE (' + include_definition + N')'
                        ELSE N''
                        END
                    /* filters */
                    + CASE 
                        WHEN si.filter_definition IS NOT NULL
                            THEN N' WHERE ' + si.filter_definition
                        ELSE N''
                        END
                    /* With */
                    + CASE 
                        WHEN is_primary_key <> 1 AND not(si.[type] in (5, 6))
                            THEN N' WITH(FILLFACTOR=100, STATISTICS_NORECOMPUTE=ON, ONLINE=OFF) '
                        ELSE N''
                        END
                    /* ON where? */
                    + N' ON ' + N'[f' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N']' + N';'
                END AS index_create_statement
        FROM sys.indexes AS si
        INNER JOIN sys.tables AS t ON si.object_id = t.object_id
        INNER JOIN sys.schemas AS sc ON t.schema_id = sc.schema_id
        LEFT JOIN sys.filegroups AS fg ON si.data_space_id = fg.data_space_id
        /* Key list */
        OUTER APPLY (
            SELECT STUFF((
                        SELECT N', ' + QUOTENAME(c.NAME) + CASE ic.is_descending_key
                                WHEN 1
                                    THEN N' DESC'
                                ELSE N''
                                END
                        FROM sys.index_columns AS ic
                        JOIN sys.columns AS c ON ic.column_id = c.column_id
                            AND ic.object_id = c.object_id
                        WHERE ic.object_id = si.object_id
                            AND ic.index_id = si.index_id
                            AND ic.key_ordinal > 0
                        ORDER BY ic.key_ordinal
                        FOR XML PATH(N'')
                            ,TYPE
                        ).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'')
            ) AS keys(key_definition)
        /* Include list */ 
        OUTER APPLY (
            SELECT STUFF((
                        SELECT N', ' + QUOTENAME(c.NAME)
                        FROM sys.index_columns AS ic
                        JOIN sys.columns AS c ON ic.column_id = c.column_id
                            AND ic.object_id = c.object_id
                        WHERE ic.object_id = si.object_id
                            AND ic.index_id = si.index_id
                            AND ic.is_included_column = 1
                        ORDER BY c.NAME
                        FOR XML PATH('')
                            ,TYPE
                        ).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'')
            ) AS includes(include_definition)
        Cross apply 
            (
            VALUES (t.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N'_in')
                  ,(t.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N'_out')
            ) t2(NAME)
        WHERE si.type IN (
                --0
                 1
                ,2
                ,5
                ,6
                ) -- heap, clustered, nonclustered, CLUSTERED COLUMNSTORE, NONCLUSTERED COLUMNSTORE
            and t.schema_id = schema_id(N'dbo')
            and exists (Select 1 from [load].[tables_to_load] tl Where t.[name] = tl.[name])
        ORDER BY t2.NAME, index_create_statement
        FOR XML PATH(N''), TYPE
        ).value(N'.', N'NVARCHAR(MAX)');
 
exec(@sql);

Begin Tran -- переключим все таблицы разом
 
DECLARE @sql NVARCHAR(max) = N'';
 
-- Переключение партиции из целевой таблицы в таблицу out и in
Select @sql = (
    Select 
          'Alter table [dbo].' + QUOTENAME(aa.name) + ' switch partition ' + cast(p.num as varchar(255)) + ' to [tmp].' +  QUOTENAME(t2.NAME + N'_out')  + ' WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1, ABORT_AFTER_WAIT = BLOCKERS));'
        + 'Alter table [tmp].' +  QUOTENAME(t2.NAME + N'_in') + ' switch to dbo.' + QUOTENAME(aa.name) + ' partition ' + cast(p.num as varchar(255)) + ' WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1, ABORT_AFTER_WAIT = BLOCKERS));;'
        FROM 
        [load].[tables_to_load] aa
        Cross apply (Values ($partition.[fn_region_data](@filial_id))) p(num)
        Cross apply 
            (
            VALUES (aa.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4))
            ) t2(NAME)
        For xml path(''), type).value(N'.', N'NVARCHAR(MAX)');
 
-- Т.к. возможно только одно переключение одновременно
-- установим эксклюзивную блокировку
exec sp_getapplock N'target', 'Exclusive', 'Transaction', -1;
 
exec(@sql);
 
-- Освободим защелку
exec sp_releaseapplock N'target', 'Transaction';
 
Commit

DECLARE @sql NVARCHAR(max) = N'';
-- Изменение параметров сжатия темповых таблиц, аналогично целевой таблице 
Select @sql += Coalesce((
    Select 
        ';Alter table [tmp].' + QUOTENAME(t2.NAME) + ' REBUILD PARTITION = ALL  WITH (DATA_COMPRESSION = ' + pt.data_compression_desc + ')'   
        FROM 
        sys.tables aa
        Cross apply (Values ($partition.[fn_region_data](@filial_id))) p(num)
        Cross apply 
            (
            VALUES (aa.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) +  N'_out')
            ) t2(NAME)
        inner join sys.partitions (nolock) pt on pt.partition_number = p.num and pt.object_id = aa.object_id  and pt.index_id = 1
        inner join sys.partitions (nolock) pt2 on pt2.object_id = object_id('tmp.' + t2.NAME) and pt2.index_id = 1
        WHERE aa.schema_id = schema_id(N'dbo') and pt.data_compression_desc <> pt2.data_compression_desc
              and exists(Select 1 from [load].[tables_to_load] a where a.[name] = aa.[name])
        For xml path(''), type).value(N'.', N'NVARCHAR(MAX)'), N'');
 
Select @sql += Coalesce((
    Select 
        ';Alter table [tmp].' + QUOTENAME(t2.NAME) + ' REBUILD PARTITION = ALL  WITH (DATA_COMPRESSION = ' + pt.data_compression_desc + ')'   
        FROM 
        sys.tables aa
        Cross apply (Values ($partition.[fn_region_data](@filial_id))) p(num)
        Cross apply 
            (
            VALUES (aa.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N'_in')
            ) t2(NAME)
        inner join sys.partitions (nolock) pt on pt.partition_number = p.num and pt.object_id = aa.object_id and pt.index_id = 1
        inner join sys.partitions (nolock) pt2 on pt2.object_id = object_id('tmp.' + t2.NAME) and pt2.index_id = 1
        WHERE aa.schema_id = schema_id(N'dbo') and pt.data_compression_desc <> pt2.data_compression_desc
              and exists(Select 1 from [load].[tables_to_load] a where a.[name] = aa.[name])
        For xml path(''), type).value(N'.', N'NVARCHAR(MAX)'), N'');
 
-- Создание констрейнтов, соответствующих партиции, только для in
Select @sql += Coalesce((
    Select 
        ';Alter table [tmp].' +  QUOTENAME(t2.NAME) + ' WITH CHECK ADD CONSTRAINT ' + Quotename('chk_' + t2.NAME) + ' CHECK ([filial_id] = ' + Cast(@filial_id as nvarchar(255)) + ')'
        FROM 
        sys.tables aa
        Cross apply 
            (
            VALUES (aa.[name] + '_' + Right(N'0000' + Cast(@filial_id AS NVARCHAR(4)), 4) + N'_in')
            ) t2(NAME)
        WHERE aa.schema_id = schema_id(N'dbo')
              and exists(Select 1 from [load].[tables_to_load] a where a.[name] = aa.[name])
        For xml path(''), type).value(N'.', N'NVARCHAR(MAX)'), N'');
 
exec(@sql);