使用更改跟踪(ChangeTracking)来实现数据类型变更

在现实场景中，我们经常会遇到修改数据类型的场景，尤其是自增列从INT修改为BIGINT的情况，自增列又通常作为表的主键和聚集索引键，因此修改操作需要按以下步骤来进行

1. 停止对该表的访问(通过禁用权限或停应用的方式实现)

2. 删除非聚集索引

3. 删除主键聚集索引

4. 使用ALTER TABLE ALTER COLUMN来修改

5. 创建主键聚集索引

6. 创建非聚集索引

此方式有以下缺点：

1. 整个ALTER COLUMN操作作为一个事务，需要对将每条数据修改操作记录到日志中，中途撤销修改需要长时间回滚。

2. 根据页面碎片情况，修改类型操作可能造成大量的页拆分，导致日志文件暴增。

3. 影响正常业务的周期较长。

针对大事务和页拆分的问题，可以进行以下改进：新建表，将现有表中数据导入到新表中，数据导入完成后，修改新表名称为现有表名，当仍无法解决影响周期较长的问题，在导入数据期间，允许程序进行只读访问，降低修改类型操作对业务的影响，但数据长时间不可修改，这对很多重要业务也是不可接受的。

为解决此问题，肖桑提出了复制环路的解决办法，采用多级复制的方式，将旧表数据复制到新表中，然后停止旧表读写，等所有修改同步到新表后，再修改表名，以实现数据类型变更的目的，除前期准备时间外，整个操作对业务影响时间可以控制在几分钟以内。

采用导入新表的方式，为保证新旧两表数据一致，必须限制对旧表的修改操作，如果能保证某一时间点的数据被完整地导入到新表，并且保证在导入过程中所有发生在旧表上的操作被“同步”到新表中，那便可以实现新旧两表在特定时间点数据一致。

实现方式：

1. 使用数据库快照来将旧表中某一点的数据导入到新表

2. 使用更改跟踪来将自快照后所有发生在旧表上的数据变更更新到新表上

测试步骤及测试代码：

1. 对数据库启用更改跟踪

--===================================

--对数据库TesDB2启用CT功能

ALTER DATABASE TesDB2

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

GO

2. 创建测试表和生成模拟数据

--=====================================

--创建测试表

CREATE TABLE TB001

(

    ID INT IDENTITY(1,1) PRIMARY KEY,

    C1 NVARCHAR(200)

)

GO

--==================================

--插入数据

INSERT INTO TB001(C1)

SELECT T1.name FROM sys.all_columns T1

GO

3. 对表启用更改跟踪

--===================================

--对表TB001启用CT功能

ALTER TABLE TB001

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

GO

4. 创建新表，并将数据导入到新表中

--==================================

--创建测试表

CREATE TABLE TB002

(

    ID BIGINT IDENTITY(1,1) PRIMARY KEY,

    C1 NVARCHAR(200)

)

GO

--==================================

--将TB001的数据导入到TB002中

--生产环境可以使用快照方式来保证数据一致性

SET IDENTITY_INSERT TB002 ON

INSERT TB002(ID,C1)

SELECT ID,C1 FROM TB001

SET IDENTITY_INSERT TB002 OFF

GO

5. 模拟数据库上变化，然后将禁用账户对表的修改权限

--======================================

--模拟TB001上数据变化

INSERT INTO TB001(C1)

SELECT TOP(100) T1.name

FROM sys.all_columns T1

GO

UPDATE TB001

SET C1='UPDATEDATA'

WHERE ID%10=1

GO

DELETE TB001

WHERE ID%4=1

GO

6. 将在旧表上的删除操作“同步”到新表上

--======================================

--删除TB001中不存在但在TB002中存在的数据

WITH T1 AS

(

    SELECT

        ROW_NUMBER() OVER (PARTITION BY ID

        ORDER BY CT.SYS_CHANGE_VERSION DESC) AS RowNum,

         *

    FROM CHANGETABLE(CHANGES [dbo].[TB001],0) AS CT

),

T2 AS

(

    SELECT  *

    FROM T1

    WHERE RowNum = 1

    AND SYS_CHANGE_OPERATION = 'D'

)

DELETE FROM [dbo].[TB002]

WHERE ID IN (

SELECT ID FROM T2

)

GO

7. 将在旧表上的删除操作“同步”到新表上

--======================================

--根据TB001中插入和更新的数据来更新TB002

GO

SET IDENTITY_INSERT [dbo].[TB002] ON

;WITH T1 AS

(

    SELECT

        ROW_NUMBER() OVER (PARTITION BY ID

        ORDER BY CT.SYS_CHANGE_VERSION DESC) AS RowNum,

         *

    FROM CHANGETABLE(CHANGES [dbo].[TB001],0) AS CT

),

T2 AS

(

    SELECT  *

    FROM T1

    WHERE RowNum = 1

    AND (SYS_CHANGE_OPERATION = 'U'

        OR SYS_CHANGE_OPERATION = 'I')

),

T3 AS

(

    SELECT * FROM [dbo].[TB001]

    WHERE ID IN (SELECT ID FROM T2)

),

T4 AS

(

    SELECT * FROM [dbo].[TB002]

    WHERE ID IN (SELECT ID FROM T2)

)

MERGE T4 AS T

USING T3 AS S

ON T.ID=S.ID

WHEN MATCHED THEN

    UPDATE SET T.C1=S.C1

 WHEN NOT MATCHED BY TARGET THEN

 INSERT(

            [ID],

            [C1]

        )

      VALUES

      (

            [ID],

            [C1]

        );

SET IDENTITY_INSERT [dbo].[TB002] OFF

GO

8. 检查两表数据是否一致，生产环境中建议使用SP_SPACEUSED来查看表数据

--====================================================

--检查数据是否相同,如果下面查询没有数据,则证明数据一致

SELECT * FROM

(

    SELECT * FROM TB001

    EXCEPT

    SELECT * FROM TB002

) AS T1

UNION

SELECT * FROM

(

    SELECT * FROM TB002

    EXCEPT

    SELECT * FROM TB001

) AS T2

GO

9. 清理测试环境

--==================================

--禁用表级别更改跟踪

ALTER DATABASE [TestDB2]

SET CHANGE_TRACKING = OFF

--==================================

--禁用表级别更改跟踪

ALTER TABLE TB001

DISABLE CHANGE_TRACKING;

--=====================================

--删除测试表

DROP TABLE TB001

DROP TABLE TB002

由于我们追求的是最终数据一致，因此使用ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CT.SYS_CHANGE_VERSION DESC) AS RowNum =1的方式来过滤中间变更,如果使用SYS_CHANGE_COLMNS会“严重”增加代码复杂性，因此采用更新所有字段的暴力方式实现。

如果导入数据的过程持续时间较长，该期间内数据变化较大，可以考虑先实现一次“同步”后，再禁用旧表的访问权限，然后再做一次“同步”操作，以降低最后一次“同步”的运行时间。

--============================================================

好久米写文脏，随便整个妹子镇贴。

巴特西

使用更改跟踪(ChangeTracking)来实现数据类型变更

最新文章

热门文章