将数据从常规表迁移到账本表

  • 项目

适用于： SQL Server 2022 (16.x) Azure SQL 数据库Azure SQL 托管实例

无法将常规表转换为账本表，但可以将数据从现有常规表迁移到账本表，然后将原始表替换为账本表。

执行数据库账本验证时，该过程需要对每个事务中的所有操作进行排序。 如果使用 SELECT INTO 或 BULK INSERT 语句将数十亿行从常规表复制到账本表，则所有操作都将在一个事务中完成。 这意味着需要对大量数据进行完全排序，这将在单个线程中完成。 排序操作需要很长时间才能完成。

若要将常规表转换为账本表，Microsoft 建议使用 sys.sp_copy_data_in_batches 存储过程。 这会将复制操作拆分为每个事务 10-100K 行的批处理。 因此，数据库账本验证具有可以并行排序的较小事务。 这极大地缩减了数据库账本验证时间。

注意

客户仍然可以使用其他命令、服务或工具将数据从源表复制到目标表。 请确保避免大型事务，因为这会对数据库账本验证产生性能影响。

本文介绍如何将常规表转换为账本表。

先决条件

创建仅追加或可更新账本表

在可以使用 存储过程之前，需要创建一个与源表具有相同架构的仅追加帐本表或可更新帐本表。 架构在列数、列名及其数据类型方面应相同。 TRANSACTION ID、SEQUENCE NUMBER 和 GENERATED ALWAYS 列将被忽略，因为它们是系统生成的。 表之间的索引可能不同，但目标表只能是堆表或具有聚集索引。 之后应创建非聚集索引。

假设数据库中有以下常规 Employees 表。

CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[SSN] [char](11) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Salary] [money] NOT NULL
	);

创建仅追加账本表或可更新账本表的最简单方法是编写原始表的脚本并添加 LEDGER = ON 子句。 在下面的脚本中，我们基于 Employees 表的架构创建了一个名为 Employees_LedgerTable 的新的可更新账本表。

	CREATE TABLE [dbo].[Employees_LedgerTable](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[SSN] [char](11) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Salary] [money] NOT NULL
	)
    WITH 
    (
      SYSTEM_VERSIONING = ON,
      LEDGER = ON
    );

将数据从常规表复制到账本表

存储过程 sys.sp_copy_data_in_batches 在验证其架构是否相同后，将数据从源表复制到目标表。 在单个事务中批量复制数据。 如果操作失败，将部分填充目标表。 目标表还应为空。

在下面的脚本中，我们要将数据从常规 Employees 表复制到新的可更新账本表 Employees_LedgerTable。

sp_copy_data_in_batches @source_table_name = N'Employees' , @target_table_name = N'Employees_LedgerTable'

相关内容