【转载】SQL Server FileStream 体验

FileStream是SQL Server 2008提供的新特性，之前附件在SQL的存储一种是直接放数据库，一种是存储一个路径，附件单独放在磁盘上。前一种方法会使数据库空间更快变大，而且读写占用较多数据库资源，后一种方法使附件的管理脱离于数据库，不能和数据库一起备份，不支持事务等。FileStream的引入同时解决了以上问题，即能带来文件系统读写附件的效率，又能让附件读写处在SQL Server的控制之下。

开启FileStream

操作步骤：
1、在MSSQL实例服务上右键属性，在FileStream面板上将相关选项打上勾；
2、在SSMS上运行：

-- 0表示关闭FileStream访问，1表示只允许T-SQL访问，2表示允许T-SQL和Win32访问
EXEC sp_configure filestream_access_level, 2
RECONFIGURE

 

3、重启SQL服务。

创建支持FileStream的文件组和表

为TestDB增加支持FileStream的文件组：

ALTER DATABASE TestDB ADD FILEGROUP TestDBFG CONTAINS FILESTREAM

 

为文件组添加文件，作为存储文件的路径，注意下面代码中的路径不要已经存在，否则会报错

ALTER DATABASE TestDB ADD FILE (
NAME = FileStrmFile,
FILENAME = 'C:\MyTest\FileStreamPath')
TO FILEGROUP TestDBFG

 

创建带FileStream列的表：

CREATE TABLE dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
    [SerialNumber] INTEGER UNIQUE,
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

 

执行上面代码后，发现FileStream文件夹已自动创建：

 

插入文件数据体验效果

下面的代码将一图片插入到表中

DECLARE @img AS VARBINARY(MAX)

SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            'C:\MyTest\TestPic.png',
            SINGLE_BLOB ) AS x

INSERT INTO Records(Id,SerialNumber,Chart) 
SELECT NEWID(), 1,@img

 

用sp_spaceused查看表的占用空间，远比图片本身的空间少。说明表中的二进制列存储的并不是文件本身，而是一个类似文件指针的属性数据。

关于FileStream文件的删除

如果直接运行Delete语句FileStream并不会马上被删除，这是因为SQL并不会实时对它进行维护，而是有一个垃圾回收后台进程在异步处理，当到达一个checkpoint时，才会回收不需要的数据。如果想手动触发，可以直接运行CheckPoint命令达到效果，若数据库的恢复模式是Full，还要先备份事务日志才可以。