SQL Server误区30日谈 第18天 有关FileStream的存储，垃圾回收以及其它

引言

在SQL Server 2008R2中，FileStream成为了官方推荐的大文件存储方式。FileStream存储在表格中的数据是一个BLOB(Binary Large Object)，相较于把数据存储为VARBINARY，它的优点在于可以支持一些新的文件操作，比如使用Win32文件API和Transact-SQL存储过程打开、编辑、删除大于2 GB的文件，同时，存储也更加高效。

但是，FileStream也存在一些误区，基于一些观念上的理解反而会使得实际使用中出现一些问题，本篇文章将讲解其中一些比较重要且常见的问题。

文件内容以及垃圾回收

在使用FileStream存储文件时，很多人认为删除文件后，文件的空间会被回收。但是，这种理解是错误的。在SQL Server中，文件是以文件流方式存储于磁盘，并在NTFS上留下文件占用磁盘的记录，即文件系统维持着对该文件的一个指针。当使用SQL Server内置的GC机制删除文件时，它只是把对文件的指向删除了，并不能确保该文件被彻底删除，因此，在删除数据时，需要注意一些事项：
- 如果不考虑空间问题，一般的做法是对于所有不再使用的文件手动运行DBCC SHRINKFILE (或 SHRINKDATABASE)，这样才能使数据库的物理占用得到正确的计算和管理。
- 对于文件删除的处理，在逻辑上应保证垃圾回收每次保证了至少一个回收间隔内所有的空间已经被回收。

存储实例

下面是一个存储实例：

表结构

现有一个表t_img，包含两个列，一个是ID，一个是t_img_content，数据类型为varbinary(max)。

CREATE TABLE [dbo].[t_img](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [t_img_content] [varbinary](max) FILESTREAM NULL,
 CONSTRAINT [PK_t_img] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
FILESTREAM_ON [FileStream01]

其中存储在FileStream01文件组中。

存储流程

下面是一个存储流程的示例：

DECLARE @image_path NVARCHAR(MAX);
SELECT @image_path = 'C:\images\image001.jpg'

INSERT INTO t_img(t_img_content)
SELECT *
FROM   Openrowset(Bulk 'C:\images\image001.jpg', SINGLE_BLOB) AS binarydata;

这种方式可以把图片文件直接转换为二进制格式并插入到表中。

删除文件

接下来是文件删除示例：

DELETE FROM t_img WHERE id = 1

在删除完文件之后，即使执行完DBCC SHRINKFILE指令，文件的存储空间也依然存在，对于这种情况，我们可以手动打开SQL Server的垃圾回收功能。

EXECUTE sys.sp_filestream_force_garbage_collection [FileStream01]

通过手动执行sys.sp_filestream_force_garbage_collection存储过程来清除空间，让每次回收间隔内的所有空间都被回收。

总结

FileStream是一种高效的存储方式，但是在使用中需要注意文件内容以及垃圾回收相关的问题，避免出现不必要的麻烦。同时，为了优化数据库存储和管理功能，还可以设置SQL Server的垃圾回收功能，以便更好地管理储空间。以上是本文内容的全部介绍，希望可以对您延申思路，掌握更多实用知识。