主页 / dba / 问题 / 52988
Accepted
Matthew
Asked: 2013-11-08 16:54:51 +0800 CST

在我的软删除指示器列上创建分区

  • 772

在 SQL Server 2008 R2 中，我正在探索分区表的使用。

我们有一个软删除方案，有时是一个IsDeleted bit NOT NULL，有时是一个DateDeleted date NULL

我想专注于第一种情况，即位。

我正在考虑对该表进行分区，因为该表几乎总是被查询和连接，包括该列。

我将如何进行这个过程？这是个坏主意吗？

我首先尝试单步执行向导，但它为我生成的脚本似乎很尴尬（多滴约束，创建新 CI）

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    以下是我刚刚创建的示例脚本，展示了如何创建包含IsDeleted字段的表。

    创建表并填充 10,000 行后，我添加了一个分区函数和方案，以及一些文件组和文件来包含分区。在现实生活中，您可能希望在专用 LUN 上创建这些文件组和文件，以利用额外的 I/O 和零碎恢复。

    然后我更改表，使其利用分区功能，从而将现有行移动到适当的文件组中。由于您无法执行“ALTER TABLE...ALTER CONSTRAINT”来更改主键以使用分区功能，因此您被迫删除主键并使用新的分区功能重新创建它。这确实意味着聚集索引将变成堆，直到您可以使用分区功能创建新的聚集索引。

    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;

    我刚刚运行它并得到以下结果：

    在此处输入图像描述

    如您所见，该表在IsDeleted = 0andIsDeleted = 1状态下具有几乎完美的行分布。将行从 0 更新到 1 会导致该行移动到另一个分区。

    我希望这有助于展示如何更改表以利用IsDeleted软删除BIT字段上的分区。

    当我运行向导来分区这个表时，可以选择脚本到一个新的查询窗口，我得到了这个代码：

    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

    据我所知，这段代码看起来不起作用。它删除 PK，然后重新创建它，而不使用分区功能。然后它使用分区功能创建一个聚集索引，但随后立即删除它！

    • 3

  2. 分区列必须在聚集索引中。因此，如果 IsDeleted 的值发生更改，则该行必须从磁盘的一位移动到另一位。如果您使用该标志的意图是通过执行 UPDATE 而不是 DELETE 来推迟删除的 IO 成本，则此解决方案会适得其反。有了它，您将执行 DELETE（从行的当前位置）和 INSERT 到其新位置。如果目的是隔离存档数据，那么您就是赢家。我建议您也对表的活动部分使用过滤索引。

    • 1

相关问题