《锋利的SQL（第2版）》——2.3 修改数据库

本节书摘来自异步社区出版社《锋利的SQL（第2版）》一书中的第2章，第2.3节，作者：张洪举 王晓文，更多章节内容可以访问云栖社区“异步社区”公众号查看。

2.3 修改数据库

锋利的SQL（第2版）创建数据库后，可以对其原始定义进行更改，如扩展或收缩数据库、设置数据库选项等。要修改数据库，可以使用ALTER DATABASE等语句。

2.3.1　扩展数据库和文件

默认情况下，SQL Server可根据创建数据库时定义的增长参数自动扩展数据库。也可以通过为现有数据库文件分配更多空间，或者创建新文件来手动扩展数据库。如果未将数据库设置为自动增长或硬盘上没有足够的磁盘空间，数据库已经用完分配给它的空间且不能自动增长，会出现1105错误。

扩展数据库时，必须使数据库的大小至少增加1 MB。如果扩展了数据库，则根据被扩展的文件，数据文件或事务日志文件将可以立即使用新空间。扩展数据库时，应指定允许文件增长到的最大大小。这样可防止文件无限制地增大，以致于用尽整个磁盘空间。

可以使用ALTER DATABASE语句设置数据库大小或向数据库添加文件，其语法格式如下：

ALTER DATABASE database_name 　ADD FILE <filespec> [ ,...n ] 　　　　[ TO FILEGROUP { filegroup_name | DEFAULT } ] 　| ADD LOG FILE <filespec> [ ,...n ] 　| REMOVE FILE logical_file_name 　| MODIFY FILE <filespec>

其中部分用于设置文件组的属性，语法格式如下：

( 　　NAME = logical_file_name　 　　[ , NEWNAME = new_logical_name ] 　　[ , FILENAME = 'os_file_name' ] 　　[ , SIZE = size [ KB | MB | GB | TB ] ] 　　[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 　　[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 　　[ , OFFLINE ] )

其中的OFFLINE选项用于将文件设置为脱机并使文件组中的所有对象都不可访问，仅在文件已损坏但可以还原时，才能使用该选项。其他参数选项请参考前面CREATE DATABASE语句中的说明。

例如，下面的语句用于将Sales中的SPri1_dat文件扩展到15 MB，并将最大值设置为25 MB。

ALTER DATABASE Sales MODIFY FILE ( 　　NAME = 'SPri1_dat', 　　SIZE = 15MB, 　　MAXSIZE = 25MB )

下面的语句向SalesGroup1文件组中添加一个SGrp1Fi3_dat文件。

ALTER DATABASE Sales ADD FILE ( 　　NAME = SGrp1Fi3_dat, 　　FILENAME = 'c:\SG1Fi3dt.ndf', 　　SIZE = 5MB, 　　MAXSIZE = 10MB, 　　FILEGROWTH = 5MB ) TO FILEGROUP SalesGroup1 ;

执行下面的语句则可以删除上面添加的SGrp1Fi3_dat文件。

ALTER DATABASE Sales REMOVE FILE SGrp1Fi3_dat ;

2.3.2　向数据库中添加、删除和修改文件组

下面是使用ALTER DATABASE语句向数据库中添加、删除和修改文件组时的语法格式：

ALTER DATABASE database_name 　　ADD FILEGROUP filegroup_name 　　| REMOVE FILEGROUP filegroup_name 　　| MODIFY FILEGROUP filegroup_name 　　　　{ <filegroup_updatability_option> 　　　　| DEFAULT 　　　　| NAME = new_filegroup_name 　　　　}

部分的语法格式如下：

{ READONLY | READWRITE } | { READ_ONLY | READ_WRITE }

例如，下面的语句用于向Sales数据库中添加一个名为SalesGroup3的文件组。

ALTER DATABASE Sales ADD FILEGROUP SalesGroup3 ;

下面的语句重命名文件组SalesGroup3为SalesGroup4。

ALTER DATABASE Sales MODIFY FILEGROUP SalesGroup3 NAME = SalesGroup4 ;

2.3.3　收缩数据库和文件

可以使用DBCC SHRINKDATABASE语句或DBCC SHRINKFILE语句来手动收缩数据库或数据库中的文件。数据库中的每个文件都可以通过删除未使用的页的方法来减小。尽管数据库引擎会有效地重新使用空间，但某个文件多次出现无须原来大小的情况后，收缩文件就变得很有必要了。可以成组或单独地手动收缩数据库文件，也可以设置数据库的AUTO_SHRINK选项为ON来指定按间隔自动收缩。

文件始终从末尾开始收缩。例如，如果有个5 GB的文件，并且在DBCC SHRINKFILE语句中指定为4 GB，则数据库引擎将从文件的最后一个1 GB开始释放尽可能多的空间。如果文件中被释放的部分包含使用过的页，则数据库引擎先将这些页重新放置到文件的保留部分。只能将数据库收缩到没有剩余的可用空间为止。例如，如果某个5 GB的数据库有4 GB的数据，并且在DBCC SHRINKFILE语句中指定为 3 GB，则只能释放1 GB。

在使用DBCC SHRINKDATABASE语句时，无法将整个数据库收缩得比其初始大小更小。例如，如果数据库创建时的大小为10 MB，后来增长到100 MB，则该数据库最小只能收缩到10 MB，即使已经删除数据库的所有数据也是如此。

但是，使用DBCC SHRINKFILE语句时，可以将各个数据库文件收缩得比其初始大小更小。必须对每个文件分别进行收缩，而不能尝试收缩整个数据库。

1．手动收缩数据库下面是DBCC SHRINKDATABASE语句的语法格式：

DBCC SHRINKDATABASE ( 'database_name' | database_id | 0 　　 [ ,target_percent ] 　　 [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

'database_name' | database_id | 0

要收缩的数据库的名称或ID。如果指定0，则使用当前数据库。

target_percent

数据库收缩后的数据库文件中所需的剩余可用空间百分比。

NOTRUNCATE

指定在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。

TRUNCATEONLY

将数据文件中任何未使用空间释放给操作系统，并将文件收缩到最后分配的区，从而无须移动任何数据即可减小文件大小。使用TRUNCATEONLY时，将忽略target_percent设置。

WITH NO_INFOMSGS

取消严重级别从0到10的所有信息性消息。

下面的语句使Sales数据库中文件有10%的可用空间。

DBCC SHRINKDATABASE ('Sales', 10)

2．使用ALTER DATABASE设置自动收缩数据库将数据库的AUTO_SHRINK选项设置为ON后，数据库引擎将自动收缩有可用空间的数据库。下面是使用ALTER DATABASE语句将Sales数据库的AUTO_SHRINK选项设置为ON的方法。

ALTER DATABASE Sales SET AUTO_SHRINK ON ;

3．收缩文件下面是DBCC SHRINKFILE语句的语法格式：

DBCC SHRINKFILE ( 　　{ 'file_name' | file_id } 　　{ [ , EMPTYFILE ] 　　| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] 　　} ) [ WITH NO_INFOMSGS ]

'file_name'

要收缩的文件的逻辑名称。

file_id

要收缩的文件的标识（ID）号。可以使用FILE_ID函数获取文件的ID。

target_size

用兆字节表示的文件大小。如果未指定，则DBCC SHRINKFILE将文件大小减少到默认文件大小。

EMPTYFILE

将指定文件中的所有数据迁移到同一文件组中的其他文件。

NOTRUNCATE

将释放的文件空间保留在文件中。当与target_size一起指定NOTRUNCATE时，释放的空间不会释放给操作系统。唯一影响是将已使用的页从target_size行前面重新定位到文件的前面。

TRUNCATEONLY

将文件中的任何未使用空间释放给操作系统，并将文件收缩到最后一次分配的区，从而减小了文件大小，但是没有移动任何数据。不会尝试将行重新定位到未分配的页。使用TRUNCATEONLY时，将忽略target_size。

WITH NO_INFOMSGS

禁止显示所有信息性消息。

例如，下面的语句将Sales数据库中的SPri1_dat文件的大小收缩到8 MB。

USE Sales ; GO DBCC SHRINKFILE (SPri1_dat, 8) ;

以下示例演示了清空文件以便从数据库中将其删除的步骤。针对此示例，首先创建一个数据文件，并假设该文件包含数据。

USE AdventureWorks; GO -- 创建一个数据文件并假设其包含数据 ALTER DATABASE AdventureWorks ADD FILE ( 　　NAME = Test1data, 　　FILENAME = 'C:\t1data.ndf', 　　SIZE = 5MB 　　); GO -- 清空数据文件 DBCC SHRINKFILE (Test1data, EMPTYFILE); GO

-- 从数据库中移除数据文件ALTER DATABASE AdventureWorksREMOVE FILE Test1data;GO

2.3.4　设置数据库选项

可以在新建数据库时或对现有数据库通过“数据库属性”窗口进行部分数据库选项设置，而使用ALTER DATABASE的SET子句则可以进行更加全面的选项设置。可用的设置选项如表2-1所示。表2-1　可用的数据库设置选项

2.3　修改数据库

创建数据库后，可以对其原始定义进行更改，如扩展或收缩数据库、设置数据库选项等。要修改数据库，可以使用ALTER DATABASE等语句。

2.3.1　扩展数据库和文件

默认情况下，SQL Server可根据创建数据库时定义的增长参数自动扩展数据库。也可以通过为现有数据库文件分配更多空间，或者创建新文件来手动扩展数据库。如果未将数据库设置为自动增长或硬盘上没有足够的磁盘空间，数据库已经用完分配给它的空间且不能自动增长，会出现1105错误。

扩展数据库时，必须使数据库的大小至少增加1 MB。如果扩展了数据库，则根据被扩展的文件，数据文件或事务日志文件将可以立即使用新空间。扩展数据库时，应指定允许文件增长到的最大大小。这样可防止文件无限制地增大，以致于用尽整个磁盘空间。

可以使用ALTER DATABASE语句设置数据库大小或向数据库添加文件，其语法格式如下：

ALTER DATABASE database_name 　ADD FILE <filespec> [ ,...n ] 　　　　[ TO FILEGROUP { filegroup_name | DEFAULT } ] 　| ADD LOG FILE <filespec> [ ,...n ] 　| REMOVE FILE logical_file_name 　| MODIFY FILE <filespec>

其中部分用于设置文件组的属性，语法格式如下：

( 　　NAME = logical_file_name　 　　[ , NEWNAME = new_logical_name ] 　　[ , FILENAME = 'os_file_name' ] 　　[ , SIZE = size [ KB | MB | GB | TB ] ] 　　[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 　　[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 　　[ , OFFLINE ] )

其中的OFFLINE选项用于将文件设置为脱机并使文件组中的所有对象都不可访问，仅在文件已损坏但可以还原时，才能使用该选项。其他参数选项请参考前面CREATE DATABASE语句中的说明。

例如，下面的语句用于将Sales中的SPri1_dat文件扩展到15 MB，并将最大值设置为25 MB。

ALTER DATABASE Sales MODIFY FILE ( 　　NAME = 'SPri1_dat', 　　SIZE = 15MB, 　　MAXSIZE = 25MB )

下面的语句向SalesGroup1文件组中添加一个SGrp1Fi3_dat文件。

ALTER DATABASE Sales ADD FILE ( 　　NAME = SGrp1Fi3_dat, 　　FILENAME = 'c:\SG1Fi3dt.ndf', 　　SIZE = 5MB, 　　MAXSIZE = 10MB, 　　FILEGROWTH = 5MB ) TO FILEGROUP SalesGroup1 ;

执行下面的语句则可以删除上面添加的SGrp1Fi3_dat文件。

ALTER DATABASE Sales REMOVE FILE SGrp1Fi3_dat ;

2.3.2　向数据库中添加、删除和修改文件组

下面是使用ALTER DATABASE语句向数据库中添加、删除和修改文件组时的语法格式：

ALTER DATABASE database_name 　　ADD FILEGROUP filegroup_name 　　| REMOVE FILEGROUP filegroup_name 　　| MODIFY FILEGROUP filegroup_name 　　　　{ <filegroup_updatability_option> 　　　　| DEFAULT 　　　　| NAME = new_filegroup_name 　　　　}

部分的语法格式如下：

{ READONLY | READWRITE } | { READ_ONLY | READ_WRITE }例如，下面的语句用于向Sales数据库中添加一个名为SalesGroup3的文件组。

ALTER DATABASE Sales ADD FILEGROUP SalesGroup3 ;

下面的语句重命名文件组SalesGroup3为SalesGroup4。

ALTER DATABASE Sales MODIFY FILEGROUP SalesGroup3 NAME = SalesGroup4 ;

2.3.3　收缩数据库和文件

可以使用DBCC SHRINKDATABASE语句或DBCC SHRINKFILE语句来手动收缩数据库或数据库中的文件。数据库中的每个文件都可以通过删除未使用的页的方法来减小。尽管数据库引擎会有效地重新使用空间，但某个文件多次出现无须原来大小的情况后，收缩文件就变得很有必要了。可以成组或单独地手动收缩数据库文件，也可以设置数据库的AUTO_SHRINK选项为ON来指定按间隔自动收缩。

文件始终从末尾开始收缩。例如，如果有个5 GB的文件，并且在DBCC SHRINKFILE语句中指定为4 GB，则数据库引擎将从文件的最后一个1 GB开始释放尽可能多的空间。如果文件中被释放的部分包含使用过的页，则数据库引擎先将这些页重新放置到文件的保留部分。只能将数据库收缩到没有剩余的可用空间为止。例如，如果某个5 GB的数据库有4 GB的数据，并且在DBCC SHRINKFILE语句中指定为 3 GB，则只能释放1 GB。

在使用DBCC SHRINKDATABASE语句时，无法将整个数据库收缩得比其初始大小更小。例如，如果数据库创建时的大小为10 MB，后来增长到100 MB，则该数据库最小只能收缩到10 MB，即使已经删除数据库的所有数据也是如此。

但是，使用DBCC SHRINKFILE语句时，可以将各个数据库文件收缩得比其初始大小更小。必须对每个文件分别进行收缩，而不能尝试收缩整个数据库。

1．手动收缩数据库下面是DBCC SHRINKDATABASE语句的语法格式：

DBCC SHRINKDATABASE ( 'database_name' | database_id | 0 　　 [ ,target_percent ] 　　 [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

'database_name' | database_id | 0

要收缩的数据库的名称或ID。如果指定0，则使用当前数据库。

target_percent

数据库收缩后的数据库文件中所需的剩余可用空间百分比。

NOTRUNCATE

指定在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。

TRUNCATEONLY

将数据文件中任何未使用空间释放给操作系统，并将文件收缩到最后分配的区，从而无须移动任何数据即可减小文件大小。使用TRUNCATEONLY时，将忽略target_percent设置。

WITH NO_INFOMSGS

取消严重级别从0到10的所有信息性消息。

下面的语句使Sales数据库中文件有10%的可用空间。

DBCC SHRINKDATABASE ('Sales', 10)2．使用ALTER DATABASE设置自动收缩数据库将数据库的AUTO_SHRINK选项设置为ON后，数据库引擎将自动收缩有可用空间的数据库。下面是使用ALTER DATABASE语句将Sales数据库的AUTO_SHRINK选项设置为ON的方法。

ALTER DATABASE Sales SET AUTO_SHRINK ON ;

3．收缩文件下面是DBCC SHRINKFILE语句的语法格式：

DBCC SHRINKFILE ( 　　{ 'file_name' | file_id } 　　{ [ , EMPTYFILE ] 　　| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] 　　} ) [ WITH NO_INFOMSGS ]

'file_name'

要收缩的文件的逻辑名称。

file_id

要收缩的文件的标识（ID）号。可以使用FILE_ID函数获取文件的ID。

target_size

用兆字节表示的文件大小。如果未指定，则DBCC SHRINKFILE将文件大小减少到默认文件大小。

EMPTYFILE

将指定文件中的所有数据迁移到同一文件组中的其他文件。

NOTRUNCATE

将释放的文件空间保留在文件中。当与target_size一起指定NOTRUNCATE时，释放的空间不会释放给操作系统。唯一影响是将已使用的页从target_size行前面重新定位到文件的前面。

TRUNCATEONLY

将文件中的任何未使用空间释放给操作系统，并将文件收缩到最后一次分配的区，从而减小了文件大小，但是没有移动任何数据。不会尝试将行重新定位到未分配的页。使用TRUNCATEONLY时，将忽略target_size。

WITH NO_INFOMSGS

禁止显示所有信息性消息。

例如，下面的语句将Sales数据库中的SPri1_dat文件的大小收缩到8 MB。

USE Sales ; GO DBCC SHRINKFILE (SPri1_dat, 8) ;

以下示例演示了清空文件以便从数据库中将其删除的步骤。针对此示例，首先创建一个数据文件，并假设该文件包含数据。

USE AdventureWorks; GO -- 创建一个数据文件并假设其包含数据 ALTER DATABASE AdventureWorks ADD FILE ( 　　NAME = Test1data, 　　FILENAME = 'C:\t1data.ndf', 　　SIZE = 5MB 　　); GO -- 清空数据文件 DBCC SHRINKFILE (Test1data, EMPTYFILE); GO -- 从数据库中移除数据文件 ALTER DATABASE AdventureWorks REMOVE FILE Test1data; GO

2.3.4　设置数据库选项

可以在新建数据库时或对现有数据库通过“数据库属性”窗口进行部分数据库选项设置，而使用ALTER DATABASE的SET子句则可以进行更加全面的选项设置。可用的设置选项如表2-1所示。

例如，下面的语句设置Sales数据库的ANSI_NULLS和ANSI_NULL_DEFAULT选项为ON。

ALTER DATABASE Sales SET ANSI_NULLS ON,ANSI_NULL_DEFAULT ON ;

2.3.5　重命名数据库

在SQL Server中，可以更改数据库的名称。在重命名数据库之前，应该确保没有人使用该数据库，而且该数据库设置为单用户模式。

下面是使用ALTER DATABASE语句重命名数据库时的语法格式：

ALTER DATABASE database_name MODIFY NAME = new_database_name ; 例如，下面语句将Sales数据库重命名为Sales1。 ALTER DATABASE Sales SET SINGLE_USER;　--设置为单用户 GO ALTER DATABASE Sales MODIFY NAME = Sales1;　 --重命名为Sales1 GO ALTER DATABASE Sales1　--重新设置为多用户 SET MULTI_USER;

| SUPPLEMENTAL_LOGGING | 指定为ON时，会将详细信息添加到第三方产品的日志中；指定为OFF时，则不将详细信息添加到日志中 默认值为OFF |

例如，下面的语句设置Sales数据库的ANSI_NULLS和ANSI_NULL_DEFAULT选项为ON。

ALTER DATABASE Sales SET ANSI_NULLS ON,ANSI_NULL_DEFAULT ON ;

2.3.5　重命名数据库

在SQL Server中，可以更改数据库的名称。在重命名数据库之前，应该确保没有人使用该数据库，而且该数据库设置为单用户模式。

下面是使用ALTER DATABASE语句重命名数据库时的语法格式： ALTER DATABASE database_name MODIFY NAME = new_database_name ;

例如，下面语句将Sales数据库重命名为Sales1。

ALTER DATABASE Sales SET SINGLE_USER;　--设置为单用户 GO ALTER DATABASE Sales MODIFY NAME = Sales1;　 --重命名为Sales1 GO ALTER DATABASE Sales1　--重新设置为多用户 SET MULTI_USER; ```