Criando uma partição na minha coluna indicadora de exclusão reversível

A seguir está um script de amostra que acabei de criar que mostra como criar uma tabela que contém um IsDeletedcampo.

Depois que a tabela é criada e preenchida com 10.000 linhas, adiciono uma função e um esquema de particionamento e alguns grupos de arquivos e arquivos para conter as partições. Na vida real, você gostaria de criar esses grupos de arquivos e arquivos em LUNs dedicados para aproveitar as E/S extras e a restauração fragmentada.

Em seguida, altero a tabela para que ela use a função de particionamento, movendo assim as linhas existentes para o grupo de arquivos apropriado. Como você não pode executar um 'ALTER TABLE...ALTER CONSTRAINT` para alterar a chave primária para usar a função de particionamento, você é forçado a descartar a chave primária e recriá-la com a nova função de particionamento. Isso significa que o índice clusterizado será transformado em um heap até que você possa criar o novo índice clusterizado com a função de particionamento.

USE Test;
GO
CREATE TABLE IsDeletedTest
(
    IsDeletedTestID INT NOT NULL CONSTRAINT PK_IsDeletedTest PRIMARY KEY
        CLUSTERED IDENTITY(1,1)
    , IsDeleted BIT NOT NULL CONSTRAINT DF_IsDeletedTest_IsDeleted 
                DEFAULT ((0))
);
GO
/* INSERT 10,000 rows into IsDeletedTest */
INSERT INTO IsDeletedTest (IsDeleted) VALUES (CAST(RAND() * 2 AS INT));
GO 10000 
ALTER DATABASE Test ADD FILEGROUP IsDeleted0FG;
ALTER DATABASE Test ADD FILE 
    (NAME='IsDeletedFile0', FILENAME='C:\SQLServer\Data\IsDeletedFile0.mdf') 
    TO FILEGROUP IsDeleted0FG;
ALTER DATABASE Test ADD FILEGROUP IsDeleted1FG;
ALTER DATABASE Test ADD FILE 
    (NAME='IsDeletedFile1', FILENAME='C:\SQLServer\Data\IsDeletedFile1.mdf') 
    TO FILEGROUP IsDeleted1FG;
ALTER DATABASE Test ADD FILEGROUP IsDeleted2FG;
ALTER DATABASE Test ADD FILE 
    (NAME='IsDeletedFile2', FILENAME='C:\SQLServer\Data\IsDeletedFile2.mdf') 
    TO FILEGROUP IsDeleted1FG;
GO
/* Create a partition function using a BIT datatype for the valid values
     of 1 and 0 */
CREATE PARTITION FUNCTION IsDeletedPF (BIT)
AS RANGE LEFT FOR VALUES (0,1);

/* Create a partition scheme that implements the partition function */
CREATE PARTITION SCHEME IsDeletedPS AS PARTITION IsDeletedPF 
    TO (IsDeleted0FG, IsDeleted1FG, IsDeleted2FG);

/* MOVE the clustered index into the various filegroups 
    according to partition scheme */
ALTER TABLE IsDeletedTest DROP CONSTRAINT PK_IsDeletedTest 
    WITH (MOVE TO IsDeletedPS(IsDeleted));
GO
ALTER TABLE IsDeletedTest ADD CONSTRAINT PK_IsDeletedTest 
    PRIMARY KEY CLUSTERED (IsDeleted, IsDeletedTestID) 
    WITH (
                     ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON,
             FILLFACTOR=100 /* adjust this as necessary */
                 ) 
    ON IsDeletedPS(IsDeleted);


/* Show the movement of a single row from partition 1 to partition 2 */
SELECT IsDeleted, COUNT(1) FROM dbo.IsDeletedTest GROUP BY IsDeleted;
SELECT O.Name, PS.partition_number, PS.row_count 
FROM sys.dm_db_partition_stats PS
    INNER JOIN sys.objects O ON PS.object_id = O.object_id
WHERE O.Name = 'IsDeletedTest';
UPDATE dbo.IsDeletedTest 
    SET IsDeleted = CASE WHEN IsDeleted = 0 THEN 1 ELSE 0 END 
    WHERE IsDeletedTestID = 10000;
SELECT IsDeleted, COUNT(1) FROM dbo.IsDeletedTest GROUP BY IsDeleted;
SELECT O.Name, PS.partition_number, PS.row_count 
FROM sys.dm_db_partition_stats PS
    INNER JOIN sys.objects O ON PS.object_id = O.object_id
WHERE O.Name = 'IsDeletedTest';
GO

DROP TABLE IsDeletedTest;
DROP PARTITION SCHEME IsDeletedPS;
DROP PARTITION FUNCTION IsDeletedPF;
ALTER DATABASE Test REMOVE FILE IsDeletedFile0;
ALTER DATABASE Test REMOVE FILE IsDeletedFile1;
ALTER DATABASE Test REMOVE FILE IsDeletedFile2;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted0FG;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted1FG;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted2FG;

Acabei de executar isso e obtive os seguintes resultados:

Como você pode ver, a tabela tinha uma distribuição quase perfeita de linhas no estado IsDeleted = 0and IsDeleted = 1. A atualização de uma linha de 0 para 1 resultou na movimentação da linha para a outra partição.

Espero que isso ajude a mostrar como alterar uma tabela para aproveitar o particionamento em um campo IsDeletedde exclusão BITreversível.

Quando executei o assistente para particionar esta tabela, com a opção de script para uma nova janela de consulta, obtive este código:

USE [Test]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[IsDeletedTest] DROP CONSTRAINT [PK_IsDeletedTest];

ALTER TABLE [dbo].[IsDeletedTest] ADD  CONSTRAINT [PK_IsDeletedTest] PRIMARY KEY NONCLUSTERED 
(
    [IsDeletedTestID] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , IGNORE_DUP_KEY = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    ) 
ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]
(
    [IsDeleted]
) WITH (
    SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    ) 
ON [IsDeletedPS]([IsDeleted])

DROP INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]

COMMIT TRANSACTION

Tanto quanto eu posso dizer, este código parece que não funciona. Ele descarta o PK e o recria, sem usar a função de particionamento. Em seguida, ele cria um índice clusterizado, usando a função de particionamento, mas o descarta imediatamente!

A coluna de particionamento deve estar no índice de agrupamento. Portanto, se o valor de IsDeleted mudar, a linha deverá se mover de um bit do disco para outro. Se sua intenção ao usar o sinalizador era adiar o custo de E/S da exclusão executando um UPDATE em vez de um DELETE, essa solução é contraproducente. Com ele você estará realizando tanto um DELETE (do local atual da linha) quanto um INSERT para seu novo local. Se a intenção era isolar os dados do arquivo, você é um vencedor. Sugiro que você também use índices filtrados na parte ativa de sua tabela.