Dividindo partições existentes no SQL 2014

Abaixo está um script de exemplo para criar as novas partições anuais. Você pode precisar ajustar o script para o nome da função de partição real e novos nomes de grupos de arquivos. Esse método pressupõe que a operação é executada em uma janela de manutenção quando não há inserções na tabela e os dados de 2016 e posteriores não são necessários para consultas.

O resumo das etapas executadas pelo script são:

• crie uma nova função de partição e esquema como os originais
• crie uma nova tabela de preparo alinhada como a original
• mude a partição que contém dados > 2016-01-01 para a tabela de teste
• adicione 3 novas partições à função/esquema de partição original
• reconstruir a tabela de preparo na partição original com DROP_EXISTING=ON
• troque as 3 novas partições da tabela de preparo de volta para a tabela original

Há alguns problemas com o particionamento de tabela existente dos quais você deve estar ciente. Por causa da RANGE LEFTfunção, os valores de data e hora que são exatamente meia-noite no primeiro dia do ano serão colocados na partição errada. Por exemplo, uma linha com Timeo valor '2012-01-01T00:00:00' estará na partição 1 junto com os dados do ano 2011 em vez da partição 2 com o restante dos dados do ano 2012 conforme desejado. Isso pode não ser uma preocupação, a menos que você limpe os dados por partição.

Esteja ciente de que a última partição se uma RANGE LEFTfunção nunca pode ser removida. A implicação é que o grupo de arquivos da última partição faz parte permanentemente dos esquemas de partição que usam a função. No seu caso, o grupo de arquivos Year6FileGroupsempre será o último grupo de arquivos do psYearlyPartition_Logesquema, embora também possa ser usado para outra partição, como para o ano de 2016 aqui.

As partições após a execução do script serão como abaixo e espera-se que a última partição esteja vazia (assumindo que você não tenha dados do ano futuro). Você deve criar a partição para o próximo ano antes do início do ano para evitar dividir uma partição não vazia ou evitar pular por esses aros novamente.

+------------------+----------------+----------------+----------------+
| Partition Number | Lower Boundary | Upper Boundary |   Filegroup    |
+------------------+----------------+----------------+----------------+
|                1 | NULL           | <= 2012-01-01  | Year1FileGroup |
|                2 | > 2012-01-01   | <= 2013-01-01  | Year2FileGroup |
|                3 | > 2013-01-01   | <= 2014-01-01  | Year3FileGroup |
|                4 | > 2014-01-01   | <= 2015-01-01  | Year4FileGroup |
|                5 | > 2015-01-01   | <= 2016-01-01  | Year5FileGroup |
|                6 | > 2016-01-01   | <= 2017-01-01  | Year6FileGroup |
|                7 | > 2017-01-01   | <= 2018-01-01  | Year7FileGroup |
|                8 | > 2018-01-01   | <= 2019-01-01  | Year8FileGroup |
|                9 | > 2019-01-01   | NULL           | Year6FileGroup |
+------------------+----------------+----------------+----------------+

Eu geralmente recomendo uma RANGE RIGHTfunção ao particionar em valores incrementais como datetime porque o comportamento é mais intuitivo. Consulte Práticas recomendadas de particionamento de tabela para esta e outras considerações.

--create identical partition function and scheme
CREATE PARTITION FUNCTION pfYearlyPartition_Log_Staging(datetime) 
    AS RANGE LEFT FOR VALUES(
      '20120101'
    , '20130101'
    , '20140101'
    , '20150101'
    , '20160101'
);
CREATE PARTITION SCHEME psYearlyPartition_Log_Staging
    AS PARTITION pfYearlyPartition_Log_Staging TO (
      Year1FileGroup
    , Year2FileGroup
    , Year3FileGroup
    , Year4FileGroup
    , Year5FileGroup
    , Year6FileGroup
);

--create identical aligned staging table
CREATE TABLE [dbo].[Log_Staging](
      [RefNo] [nchar](7) NOT NULL,
      [DevID] [nvarchar](7) NOT NULL,
      [Time] [datetime] NOT NULL,
      [summary] [float] NULL,
      [staging] [float] NULL,
      [position] [float] NULL,
CONSTRAINT [PK_Log_Staging] PRIMARY KEY CLUSTERED
(
      [RefNo] ASC,
      [DevID] ASC,
      [Time] ASC
) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)
) ON psYearlyPartition_Log_Staging(Time);

--switch last partition into staging table
ALTER TABLE dbo.Log
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20170101')
    TO dbo.Log_Staging PARTITION $PARTITION.pfYearlyPartition_Log_Staging('20170101');

--create new partitions for main table (assuming filegroups already exist for years 7 and 8)
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year6FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20170101');
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20180101');
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year8FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20190101');

--we must specify IGNORE_DUP_KEY=OFF in order to use DROP_EXISTING_ON here
--but, since the original table PK index definition is unchanged, that's a non-issue
CREATE UNIQUE CLUSTERED INDEX [PK_Log_Staging] ON dbo.Log_Staging 
(
      [RefNo] ASC,
      [DevID] ASC,
      [Time] ASC
) 
WITH (DROP_EXISTING=ON, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)
ON psYearlyPartition_Log(Time);

ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20170101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20170101');
ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20180101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20180101');
ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20190101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20190101');