如何在SQL Server中管理SQL数据库的文件组

迪鲁宾
167 阅读5分钟

在这篇文章中，我们将学习如何在SQL Server中管理SQL数据库的文件组。SQL Server有四个文件组。

  1. 主文件组：主文件组是一个默认的文件组。当我们创建一个新的SQL数据库时，主文件组被自动创建。
  2. 二级/用户定义的文件组：二级文件组是由用户创建的。二级文件组用于管理数据库的数据。如果你想把你的高访问量的表保存在更快的磁盘中，你可以创建一个二级文件组并在其中创建一个表。
  3. 内存优化的文件组：内存优化的文件组用于存储内存OLTP表和表变量。为了在SQL数据库中创建一个内存优化的表，我们必须创建一个内存优化的文件组。我们将在下一篇文章中进一步了解内存优化的文件组。
  4. FILESTREAM文件组：创建FILESTREAM文件组是为了托管FILESTREAM数据和FILETABLES。我将在下一篇文章中解释更多关于FILESTRAEAM文件组的信息。

文件组包含一个或多个数据文件。这篇文章解释了我们如何管理 我们将了解以下使用情况。

  1. 在文件组中添加数据文件
  2. 重命名文件组
  3. 改变默认的文件组

为了演示上述场景，我在笔记本电脑上安装了SQL Server 2019，并恢复了Stackoverflow2010样本数据库。你可以从这里下载Stackoverflow2010数据库。我还在数据库中创建了一个名为FG_POSTS的文件组。你可以通过执行以下查询来查看文件组的列表和与文件组相关的数据文件列表。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id

输出

View list of filegroup of SQL database

现在，让我们了解所有的用例。

在一个文件组中添加数据文件

我们可以使用ALTER DATABASE ADD FILE TO FILEGRAOUP语句。语法如下。


  ALTER DATABASE <db_name> ADD FILE (NAME= <logical_file_name>, FILENAME =<file_location>, SIZE=<File_size>, FILEGROWTH= <datafile_growth>)
  To FILEGROUP <file_group_name>

在语法上。

  1. db_name：指定你要添加数据文件的数据库名称。db_name必须在ALTER DATABASE关键字之后指定。
  2. logical_file_name：指定二级数据文件的逻辑名称。
  3. file_location:指定数据文件的路径。
  4. file_size：指定数据文件的初始大小。大小参数的单位可以是KB/MB/GB。
  5. datafile_growth：指定数据文件的增长。FILEGROWTH参数的单位可以是KB/MB/GB。
  6. file_group_name：指定你想添加数据文件的文件组名称。

例如，我们想在FG_POSTS文件组中添加一个数据文件，选项如下

  1. 逻辑文件名：FG_posts_data
  2. 数据文件的位置：D:\FG_Posts
  3. data_file_name：fg_posts_data.ndf
  4. 初始大小：10MB
  5. 增长：5MB

添加数据文件的查询方法如下。

 ALTER DATABASE [Stackoverflow2010] ADD FILE ( NAME = N'fg_posts_data', FILENAME = N'D:\FG_Posts\fg_posts_data.ndf' , SIZE = 10MB , FILEGROWTH = 5MB ) 
 TO FILEGROUP [FG_POSTS]
 GO

一旦命令被执行，成功打开D:\FG_Posts来查看数据文件。

[![View secondary data file](https://s33046.pcdn.co/wp-content/uploads/2022/08/view-secondary-data-file.png)](https://s33046.pcdn.co/wp-content/uploads/2022/08/view-secondary-data-file.png)

正如你所看到的，该文件已经被创建。另外，你可以运行下面的查询来查看文件组的情况。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id

查询输出

View filegroup in SQL Database

正如你所看到的，名为 fg_posts_data的数据文件已经被创建。

现在，让我们了解如何重命名现有的文件组。

重命名现有的文件组

你可以使用ALTER DATABASE MODIFY FILEGROUP语句。以下是其语法。

  ALTER DATABASE <db_name> MODIFY FILEGROUP FILEGROUP <file_group_name> NAME= <file_group_new_name>

在语法上。

  1. db_name：指定创建文件组的数据库名称。数据库名称必须在ALTER DATABASE语句之后指定。
  2. file_group_name:指定你想重命名的文件组名称。
  3. file_group_new_name:指定文件组的新名称。

假设你想把名为FG_Posts的文件组重命名为FG_POSTS_1。查询重命名文件组的命令如下。

  ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP FG_POSTS NAME= FG_POSTS_1

一旦命令被成功执行，运行以下查询以验证更改是否被应用。

SELECT [databasefile].NAME      AS [FileName],
 [filegroup].NAME       AS [File_Group_Name],
 [filegroup].type_desc,
 physical_name [Data File Location],
 size / 128    AS [Size_in_MB],
 state_desc    [State of FILE],
 growth        [Data file growth]
FROM   sys.database_files [databasefile]
 INNER JOIN sys.filegroups [filegroup]
         ON [databasefile].data_space_id = [filegroup].data_space_id

输出

Filegroup have been renamed

正如你所看到的，文件组已经被成功重命名。

现在，让我们看看如何将一个二级文件组标记为默认文件组。

改变默认文件组

要改变默认文件组，我们可以使用ALTER DATABASE MODIFY FILEGROUP语句。以下是语法。

  ALTER DATABASE <db_name> MODIFY FILEGROUP <file_group_name> DEFAULT

在语法上。

  1. db_name：指定创建文件组的数据库名称。数据库名称必须在ALTER DATABASE语句之后指定。
  2. File_group_name：指定你想作为默认文件组的文件组名称。文件组名称必须在MODIFY FILEGROUP关键字之后指定。
  3. DEFAULT：DEFAULT关键字必须指定在文件组名称之后。

假设你想设置FG_POSTS_1为默认文件组。要做到这一点，运行下面的查询。

  ALTER DATABASE [Stackoverflow2010] MODIFY FILEGROUP [FG_POSTS_1] DEFAULT

一旦命令被成功执行，运行下面的查询来查看名为Stackoverflow2010的数据库的默认文件组。

SELECT [databasefile].NAME      AS [FileName],
  [filegroup].NAME       AS [File_Group_Name],
  [filegroup].type_desc,
  physical_name [Data File Location],
Case when is_default =1 then 'Default filegroup' else 'non-default filegroup' end [Is default Filegroup],
  size / 128    AS [Size_in_MB],
  state_desc    [State of FILE],
  growth        [Data file growth]
FROM   sys.database_files [databasefile]
  INNER JOIN sys.filegroups [filegroup]
          ON [databasefile].data_space_id = [filegroup].data_space_id

查询输出

View default filegroup

正如你所看到的，FG_POSTS_1是一个默认的文件组。

现在，让我们创建一个名为tblStackoverflow_Users的表。创建该表的脚本如下。

USE [Stackoverflow2010]
  GO
  CREATE TABLE [dbo].[tblStackoverflow_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AboutMe] [nvarchar](max) NULL,
    [Age] [int] NULL,
    [CreationDate] [datetime] NOT NULL,
    [DisplayName] [nvarchar](40) NOT NULL,
    [DownVotes] [int] NOT NULL,
    [EmailHash] [nvarchar](40) NULL,
    [LastAccessDate] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL
   CONSTRAINT [PK_tblStackoverflow_Users_Id] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  ))
  GO

现在，让我们看看这个表是在哪个文件组中创建的。要做到这一点，运行下面的查询。

use Stackoverflow2010
  go
  SELECT OBJECT_NAME(t.object_id) AS [Table Name], d.name AS [Filegroup Name]  FROM sys.data_spaces d 
  JOIN sys.indexes i on i.data_space_id = d.data_space_id
  JOIN sys.tables t on t.object_id = i.object_id
  WHERE i.index_id<2
  AND t.type = 'U'
  AND OBJECT_NAME(t.object_id) ='tblStackoverflow_Users'

输出

Filegroup name of SQL database

正如你所看到的，该表是在FG_POSTS_1文件组中创建的。

总结

在这篇文章中，我们学习了如何在SQL数据库中管理文件组。我们学习了以下几种情况。

  1. 在文件组中添加数据文件
  2. 重命名文件组
  3. 改变默认文件组
avatar
文章
阅读
粉丝