SQL Server 2005表分区实战：已存在数据表的分区迁移

操作目标

创建一个具有四个分区的数据库结构，并将现有的单一大表通过“交换”或“移动索引”的方式，将其中的数据依据特定范围分配到不同的文件组中。

准备工作与前置条件

1. 文件系统准备：在服务器本地路径 C:\Data2 下建立四个子文件夹：Primary、FG1、FG2、FG3、FG4。
2. 数据库权限：执行操作的用户需具备创建数据库、更改数据库及创建/修改索引的权限。
3. 测试环境：本操作将在新建的 Data Partition DB3 数据库中验证。

真实操作顺序

第1步：初始化多文件组数据库

首先，我们需要定义一个包含主文件组以及四个独立数据文件组的数据库结构。这是实现物理分层的基础。

• 目的：为后续分区分配空间载体。
• 关键参数：
  - 主文件组 (PRIMARY)。
  - 自定义文件组：FG1, FG2, FG3, FG4。

-- 1. 删除旧库并重建
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB3')
DROP DATABASE [Data Partition DB3]
GO

-- 2. 创建带有多个文件组的数据库
CREATE DATABASE [Data Partition DB3]
ON PRIMARY
(NAME='Data Partition DB Primary FG3',
FILENAME='C:\Data2\Primary\Data Partition DB Primary FG3.mdf',
SIZE=5MB, MAXSIZE=500MB, FILEGROWTH=1MB),
FILEGROUP [Data Partition DB3 FG1]
(NAME = 'Data Partition DB3 FG1',
FILENAME = 'C:\Data2\FG1\Data Partition DB3 FG1.ndf',
SIZE = 5MB, MAXSIZE=500MB, FILEGROWTH=1MB),
FILEGROUP [Data Partition DB3 FG2]
(NAME = 'Data Partition DB3 FG2',
FILENAME = 'C:\Data2\FG2\Data Partition DB3 FG2.ndf',
SIZE = 5MB, MAXSIZE=500MB, FILEGROWTH=1MB),
FILEGROUP [Data Partition DB3 FG3]
(NAME = 'Data Partition DB3 FG3',
FILENAME = 'C:\Data2\FG3\Data Partition DB3 FG3.ndf',
SIZE = 5MB, MAXSIZE=500MB, FILEGROWTH=1MB),
FILEGROUP [Data Partition DB3 FG4]
(NAME = 'Data Partition DB3 FG4',
FILENAME = 'C:\Data2\FG4\Data Partition DB3 FG4.ndf',
SIZE = 5MB, MAXSIZE=500MB, FILEGROWTH=1MB);
GO

第2步：构建原始数据表与测试数据

创建一个普通的非分区表，并填充不同范围的模拟数据，用于后续观察分布情况。

• 入口：USE [Data Partition DB3]。
• 动作：建表并插入测试数据。

USE [Data Partition DB3]
GO

-- 1. 创建普通表（未分区）
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money)
ON [PRIMARY] -- 明确指定放置在主文件组
GO

-- 2. 建立聚集索引（分区通常依赖聚集索引）
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID) ON [PRIMARY]
GO

-- 3. 生成测试数据
DECLARE @count int
-- 插入 ID -25 至 400 的多批次数据以覆盖不同区间
SET @count = -25
WHILE @count <= 100
BEGIN
    INSERT INTO MyTable SELECT @count, GETDATE(), 100.00
    SET @count=@count+1
END

SET @count = 101
WHILE @count <= 200
BEGIN
    INSERT INTO MyTable SELECT @count, GETDATE(), 200.00
    SET @count=@count+1
END

SET @count = 201
WHILE @count <= 300
BEGIN
    INSERT INTO MyTable SELECT @count, GETDATE(), 300.00
    SET @count=@count+1
END

SET @count = 301
WHILE @count <= 400
BEGIN
    INSERT INTO MyTable SELECT @count, GETDATE(), 400.00
    SET @count=@count+1
END

SET @count = 401
WHILE @count <= 800
BEGIN
    INSERT INTO MyTable SELECT @count, GETDATE(), 500.00
    SET @count=@count+1
END

此时查询视图 sys.partitions，确认所有数据均位于单个分区中。可以通过以下语句验证当前表的状态：
SELECT * FROM sys.partitions WHERE object_name(object_id)='MyTable'

第3步：定义分区函数与分区方案

分区函数决定了数据如何按列值切分，分区方案则将逻辑上的分区绑定到具体的物理文件组。

• 参数说明：
  - RANGE LEFT FOR VALUES (100, 200, 300)：表示左闭右开（含边界值），即 ≤100 为第一档，(100,200] 为第二档... >300 为第四档。

USE [Data Partition DB3]
GO

-- 1. 创建分区函数
-- 此处使用 RANG LEFT，意味着边界值 100, 200, 300 归入左侧较小的分区
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100, 200, 300)
GO

-- 2. 创建分区方案，将上述4个逻辑分区映射到之前创建的4个物理文件组
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data Partition DB3 FG1], [Data Partition DB3 FG2], [Data Partition DB3 FG3], [Data Partition DB3 FG4]);
GO

注意：如果使用 RANGE RIGHT，则划界标准会变为：≤99 在第一档，100~199 在第二档等。根据业务需求选择合适的边界归属至关重要。

第4步：实施表分区（移动索引）

对于已存在大量数据的表，直接修改表结构可能成本较高。最有效的方法是通过“修改聚集索引的位置”，让 SQL Server 自动重新组织底层数据的物理存储。

• 风险提醒：此过程属于重做索引操作，会产生较高的 I/O 和事务日志开销，建议在低峰期执行，并确保磁盘空间充足。
• 动作：使用 DROP EXISTING ... WITH (MOVE TO ...) 语法。

USE [Data Partition DB3]
GO

-- 核心步骤：将聚集索引移动到新的分区方案上
-- 这会自动将 MyTable 中的数据按照 Data Partition Scheme 的规则
-- 拆分并存储到对应的文件组文件中
DROP INDEX MyTable.MyTable_IXC WITH (MOVE TO [Data Partition Scheme] (ID))
GO

完成判断与排错检查

操作完成后，再次运行查看语句验证分区是否生效：

SELECT * FROM sys.partitions WHERE object_name(object_id) = 'MyTable' ORDER BY partition_number

预期结果：
如果成功，您将看到 MyTable 的总行数保持不变，但返回了 4 行记录，每行对应一个分区号（Partition Number）。您可以观察到 ID ≤100 的数据分布在第一个分区，ID 101-200 的数据分布在第二个分区，以此类推。同时，可以通过操作系统目录 C:\Data2\FGx\ 确认对应的 .ndf 文件确实包含了写入的数据块。

简要总结

通过在 SQL Server 2005 中组合使用文件组、分区函数和分区方案，并结合聚集索引的移动操作，我们可以实现对已存在数据的高效热分区。这种方式避免了复杂的 ETL 搬迁过程，非常适合海量历史数据归档场景。

以上就是SQL Server 2005表分区及数据迁移的详细内容，更多关于SQL Server数据库优化与存储管理的资料请关注本站其它相关文章！