如何使用 T-SQL 向 SQL Server 数据库添加文件组

介绍如何使用 T-SQL 的 ALTER DATABASE 语句向 SQL Server 数据库添加用户定义文件组(Filegroup),并将多个辅助数据文件分配至该文件组,实现存储优化与数据隔离。

发布于

在 SQL Server 中,数据库至少由两个操作系统文件构成:数据文件(.mdf)和事务日志文件(.ldf)。

数据文件用于存放数据库中的实际业务数据与架构对象(如表、索引、存储过程和视图等);事务日志文件则记录恢复数据库事务所必需的所有日志记录。

在创建数据库时,系统会自动建立一个名为 PRIMARY 的主文件组。主文件组包含主数据文件(.mdf)以及任何未显式指定归属文件组的辅助文件。因此,PRIMARY 默认就是主文件组。

为了提高数据库的 I/O 并发性能、方便分区维护或在不同物理磁盘间隔离存储,管理员可以创建用户定义文件组(User-Defined Filegroups),并将新增的数据文件(.ndf)按规划分配到特定文件组中。

本文演示如何使用 T-SQL 创建用户定义文件组并向其中添加数据文件。

示例:创建文件组并添加数据文件

下面示例演示使用 ALTER DATABASE ... ADD FILEGROUP 创建新文件组,并使用 ADD FILE ... TO FILEGROUP 向该文件组添加两个辅助数据文件:

USE master;
GO

-- 1. 向 Solutions 数据库添加新文件组
ALTER DATABASE Solutions  
ADD FILEGROUP Solutions1Filegroup1;  
GO  

-- 2. 向新文件组添加两个辅助数据文件
ALTER DATABASE Solutions   
ADD FILE   
(  
    NAME = Solutions_dat_2,  
    FILENAME = 'D:\mssql\data\Solutions_dat2.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5%  
),  
(  
    NAME = Solutions_dat_3,  
    FILENAME = 'D:\mssql\data\Solutions_dat3.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5% 
)  
TO FILEGROUP Solutions1Filegroup1;  
GO

此示例首先创建了一个名为 Solutions1Filegroup1 的自定义文件组,然后向该文件组中挂载了两个辅助数据文件(Solutions_dat_2 和 Solutions_dat_3),并分别配置了初始容量、最大容量和自动增长步长。

[!TIP] 将数据文件放置在不同的物理磁盘或独立挂载卷(LUN)上,可以有效平衡磁盘 I/O 争用,显著提升大规模数据写入与扫描操作的吞吐效率。

查看数据库中的文件组

添加完成后,可以通过查询 sys.filegroups 系统目录视图来确认文件组信息:

USE Solutions;
GO

SELECT 
    data_space_id,
    name, 
    type_desc,
    is_default
FROM sys.filegroups;
GO

输出:

data_space_id  name                  type_desc       is_default
-------------  --------------------  --------------  ----------
1              PRIMARY               ROWS_FILEGROUP  1
2              Solutions1Filegroup1  ROWS_FILEGROUP  0

结果表明,除了系统默认的 PRIMARY 文件组之外,新建的 Solutions1Filegroup1 文件组已经就绪。后续在创建表或聚集索引时,即可通过 ON Solutions1Filegroup1 子句将特定数据表直接指定存放在该文件组上。