Sql-server – Creating a partition on the soft-delete indicator column

partitioningsql serversql-server-2008-r2

In SQL Server 2008 R2 I am exploring the use of partitioning tables.

We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL

I want to focus on the first case, the bit.

I am considering partitioning this table on the bit because the table is almost always queried and joined including this column.

How would I go about this process? Is it a bad idea?

I first tried to step through the wizard but the script it generated for me seemed awkward (multiple drops of constraints, creation of a new CI)

Best Answer

The following is a sample script I just created that shows how to create a table that contains an IsDeleted Field.

After the table is created and filled with 10,000 rows, I add a partitioning function and scheme, and some filegroups and files to contain the partitions. In real life, you would want to create these filegroups and files on dedicated LUNs to take advantage of extra I/Os and piecemeal restore.

I then alter the table so it makes use of the partitioning function, thereby moving the existing rows into the appropriate filegroup. Since you cannot perform an 'ALTER TABLE...ALTER CONSTRAINT` to alter the primary key to use the partitioning function, you are forced to drop the primary key and recreate it with the new partitioning function. This does mean the clustered index will be turned into a heap until you can create the new clustered index with the partitioning function.

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;

I just ran this and got the following results:

enter image description here

As you can see, the table had an almost perfect distribution of rows in the IsDeleted = 0 and IsDeleted = 1 state. Updating a row from 0 to 1 resulted in the row moving to the other partition.

I hope this helps show how to alter a table to take advantage of partitioning on an IsDeleted soft-delete BIT field.

When I ran the wizard to partition this table, with the option to script to a new query window, I got this code:

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

As far as I can tell, this code looks like it doesn't work. It drops the PK, then recreates it, without using the partitioning function. It then creates a clustered index, using the partitioning function, but then immediately drops it!

Related Question