sqlserver：关于FileStream和FileTable_sqlserver filestream

环境：

• window10专业版 x64
• SqlServer2014 x64 企业版
• .net framework4.5
• 关闭防火墙

参照：

• 《官网文档 - FileStream》
• 《官网文档 - FileTable》
• 《SQL Server FileStream》
• 《SqlServer 2012 FileTable 文件表》
• 《FileTable文件表的使用》

一、FileStream篇

1.1 什么是FileStream？

FileStream是SqlServer2008引入的一种大文件存储方案。

以前我们在数据库存储大数据的时候基本上都是下面的两种模式：

• (1) 将文件存储到文件系统中，然后在表中记录下文件的路径；
• (2) 直接将二进制数据存储到列中；

现在，SqlServer2008给我们带来了第三种存储方案，它就是FileStream。
具体来说，我们可以将列指定为FileStream，然后我们就可以像之前操作二进制列一样进行增删改查，但是实际的数据已经被转移到了文件系统中，而列中只是存储了文件的句柄。

1.2 怎么使用FileStream？

SqlSerever默认没有开启FileStream，我们需要手动开启。

1.2.1 开启FileStream功能

1.2.1.1 实例级别开启FileStream：

1.2.1.2 设置数据库FileStream访问级别

这一步也可以使用sql脚本配置：
Exec sp_configure filesteam_access_level, 2
RECONFIGURE

配置完成后，重启SqlServer。

1.2.2 创建数据库

这一步主要是给数据库添加FileStream文件组，因为FileStream的数据必须存储在FileStream文件组上，而默认方式新建的数据库只有一个Primary文件组(数据行)。如果是在现有的数据库上实验的话，可以手动添加一个FileStream文件组。

准备一个空文件夹E:\test\filestream用来存放数据，如下：

下面使用sql脚本创建数据库Archive，这个数据库包含两个文件组和一个日志文件：

• 主文件组Arch1，它包含一个存储文件E:\test\filestream\archdat1.mdf；
• 另一个是FileStream文件组FileStreamGroup1，它包含一个存储位置E:\test\filestream\filestream1（它的名字叫Arch3）；
• 最后一个是日志文件。

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'E:\test\filestream\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'E:\test\filestream\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'E:\test\filestream\archlog1.ldf')
GO
1
2
3
4
5
6
7
8
9

执行上面语句后，观察目录如下：



可以看到，数据库创建后不仅创建了mdf和ldf文件，FileStream的目录也被初始化了（filestream.hdr是头文件）。

注意：
在创建数据库之前，要保证没有文件夹E:\test\filestream\filestream1。

1.2.3 创建具有FileStream功能的表

CREATE TABLE Attachment (
    [ID] uniqueidentifier rowguidcol PRIMARY KEY,
    [FileName] NVARCHAR(100) NULL,
    [CreateUser] NVARCHAR(100) NULL,
    [CreateDatetime] DATETIME NULL,
    [Content] VARBINARY(MAX) FILESTREAM NULL , 
	[Content2] VARBINARY(MAX) FILESTREAM NULL 
)
1
2
3
4
5
6
7
8

上面代码执行后，我们可以观察FileStream的目录：


注意：

在这里新建的表的主键的列类型必须是uniqueidentifier rowguidcol类型的，否则的话会报错：

1.2.4 使用c#代码测试增删改查

因为是测试的大数据存储，所以这里直接用c#代码操作了

1.2.4.1 插入数据

先准备两个测试文件如下：

c#代码：

static void TestInsert()
{
    using (SqlConnection conn = new SqlConnection("server=localhost;database=Archive;uid=sa;pwd=123456;"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            string id = Guid.NewGuid().ToString();
            cmd.CommandText = "INSERT INTO Attachment VALUES('" + id + "','C++程序设计经典案例集.pdf','test-user','" + DateTime.Now + "',@content,null)";
            var bs = File.ReadAllBytes(@"E:\test\filestream\C++程序设计经典案例集.pdf");
            SqlParameter param = new SqlParameter("content", bs);                    
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

执行后，查询数据库：

这时，我们可以去对应的文件目录里面看一下：

1.2.4.2 查询数据

static void TestQuery()
{
    var dt = new DataTable();
    using (SqlConnection conn = new SqlConnection("server=192.168.0.166;database=Archive;uid=sa;pwd=123456;"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            string id = Guid.NewGuid().ToString();
            cmd.CommandText = "select * from Attachment";
            var adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
        }
        conn.Close();
    }
    var name = dt.Rows[0]["FileName"].ToString();
    var doc = dt.Rows[0]["Content"];
    File.WriteAllBytes($@"E:\test\filestream\out-doc-name-{name}", (byte[])doc);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

执行后，我们可以看到输出如下：

1.2.4.3 修改数据

static void TestUpdate()
{
    var dt = new DataTable();
    using (SqlConnection conn = new SqlConnection("server=192.168.0.166;database=Archive;uid=sa;pwd=123456;"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            string id = Guid.NewGuid().ToString();
            cmd.CommandText = "update Attachment set Content=@content,FileName='dog.jpg'";

            SqlParameter param = new SqlParameter("content", File.ReadAllBytes(@"E:\test\filestream\dog.jpg"));
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

执行后，查看数据库：

查看FileStream对应的目录：

这里注意到，修改前的文件没有被删除，这里我们先不用理会它，SqlServer会在适当的时机自行删除的。

为了验证我们更新是否成功，我们再运行一下查询的代码，输出如下：

1.2.4.4 删除数据

我们直接在SQL Server 2014 Management Studio中执行如下语句：

delete from Attachment
1

执行后，数据就被清空了：

此时，我们观察FileStream对应的目录：

我们发现两个文件还在，这里我们也不用理会，交给SqlServer自行处理。

1.3 FileStream在备份还原中的表现

为了测试备份还原，我们运行上面的测试插入方法，重新将数据插入一次。
然后我们执行数据库的收缩，防止浪费空间：

最后执行完整备份，备份后的文件如下：

我们发现这个备份文件有70多M，很显然，FileStream中的文件也被一起备份了。
此时我们将Archive数据库删除掉，然后观察目录：

此时我们进行还原：


点击“确定”进行还原。

还原成功后，再来看看对应的目录：


由此可以看到，FileStream的内容是跟随备份还原介质的。

1.4 FileStream优缺点

优点是将大容量文件转移到文件系统中存储，但给程序代码的就好像存储在数据库一样，这样既减轻了数据库的压力，又不对代码造成影响。
缺点是并非所有的文件都适合这种方式，小于1M的文件直接存到数据库里面更合适。
另外，linux上是否支持此特性还有待实验。。。

二、FileTable篇

2.1 什么是FileTalbe？

在SqlServer2012的版本，微软将FileStream功能进行了扩展，它就是Filetable。
通过FileTable我们直接在文件系统中对文档进行新增、编辑、更新和删除等操作，并且会实时的反应到表(FileTable)中。

2.2 怎么使用FileTable？

SqlServer默认没有开启FileTable功能，我们需要先开启它。

2.2.1 开启FileTable功能

2.2.1.1 实例级别开启FileStream

同 1.2.1.1 实例级别开启FileStream。

2.2.1.2 设置数据库FileStream访问级别

同 1.2.1.2 设置数据库FileStream访问级别。

2.2.2 创建具有FileTable功能的数据库

相比仅用FileStream功能时创建数据库而言，这一步除了要给数据库添加FileStream文件组外，还要开启数据库级别的“非事务性访问”功能和设置FileStream目录名称。

准备一个空文件夹E:\test\filetable用来存放数据，如下：

下面使用sql脚本创建数据库testfiletable，这个数据库包含了一个FileStream文件组并且含有一个存储位置：E:\test\filetable\filestream1，另外在创建数据库时使用WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileStreamPath'指定了testfiletable数据库开启“非事务性访问”功能并指定FileStream目录为“FileStreamPath”：

CREATE DATABASE testfiletable 
ON
PRIMARY ( NAME = testfiletable1,
    FILENAME = 'E:\test\filetable\testfiletable.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = testfiletable2,
    FILENAME = 'E:\test\filetable\filestream1')
LOG ON  ( NAME = testfiletablelog1,
    FILENAME = 'E:\test\filetable\testfiletable.ldf')
WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileStreamPath' )  
GO
1
2
3
4
5
6
7
8
9
10

执行完成后，可以观察到目录：



其实，初始化目录这点和FileStream的创建数据库初始化没有什么不同。
不同的是下面：

如果我们想对一个现有的数据库开启FileTable功能的话，也很简单：

• 创建FIleStream文件组
• 配置“非事务性访问”和FileStream目录名称

2.2.3 创建FileTable表

注意：这里和FileStream不同。
FileStream是把功能定义在列上，而FileTable是一张特殊的表，并且这张表的列都是固定的。

执行脚本：

CREATE TABLE [DocumentStore] 
AS FileTable
WITH ( 
	--文件目录,不区分大小写,不指定则为filetable名称[DocumentStore]
	--FileTable_Directory = N'CustomPath',
	FileTable_Collate_Filename = database_default	--排序规则
);
1
2
3
4
5
6
7

创建完成后，我们可以先观察SQL Server 2014 Management Studio中的变化：

还有，这个表有哪些列，索引，约束呢？

此时，我们来观察对应的目录里：

可以看到，文件夹的初始化和FileStream时是一样的。

2.2.4 通过文件系统来管理FileTable

我们可以直接打开这个文件表对应的网络地址：

添加文件和文件夹：

我们直接从其他地方拷贝一些文件夹和文件过来，如下：

此时，我们查询数据库：

可以看到，SqlServer已经自动将所有的目录和文件映射到了表内。

此时，我们来观察FileStream对应的目录：

这里有三个文件就是刚放进去的文件，另外有一个空的文件应该是记录文件夹的（我实验的时候是全部拷贝进去的，如果是一个一个新建的文件夹可能出现3个空文件夹），我们只需要知道拷贝进去的三个文件直接存储到这里就行了，文件夹的问题不用考虑。

删除/编辑/更新文件：
在这个文件夹内，可以很方便的进行删除、编辑、更新甚至设置文件属性等操作，这个步骤比较简单，不再做实验。

2.2.5 使用c#代码测试FileTable的增删改查

2.2.5.1 查询数据

static void TestQuery()
 {
     var dt = new DataTable();
     using (SqlConnection conn = new SqlConnection("server=192.168.0.166;database=testfiletable;uid=sa;pwd=123456;"))
     {
         conn.Open();
         using (SqlCommand cmd = conn.CreateCommand())
         {
             string id = Guid.NewGuid().ToString();
             cmd.CommandText = "select * from DocumentStore where name='dog.jpg'";
             var adp = new SqlDataAdapter(cmd);
             adp.Fill(dt);
         }
         conn.Close();
     }
     var name = dt.Rows[0]["name"].ToString();
     var doc = dt.Rows[0]["file_stream"];
     File.WriteAllBytes($@"E:\test\filetable\out-doc-name-{name}", (byte[])doc);
 }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

运行完成后，输出如下：

2.2.5.2 更新数据

先准备如下文件：

更新的代码如下：

static void TestUpdate()
{
    var dt = new DataTable();
    using (SqlConnection conn = new SqlConnection("server=192.168.0.166;database=testfiletable;uid=sa;pwd=123456;"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "update DocumentStore set file_stream=@file_stream,name='海岸.jpg' where name='dog.jpg'";

            SqlParameter param = new SqlParameter("file_stream", File.ReadAllBytes(@"E:\test\filetable\海岸.jpg"));
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

运行完成后，观察数据库：

再观察文件夹：

2.2.5.3 删除数据

static void TestDelete()
{
    var dt = new DataTable();
    using (SqlConnection conn = new SqlConnection("server=192.168.0.166;database=testfiletable;uid=sa;pwd=123456;"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "delete from [DocumentStore] where name='海岸.jpg'";
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行后，观察数据库：

再观察文件夹：

2.2.5.3 插入数据

插入代码如下：

static void TestInsert()
{
    using (SqlConnection conn = new SqlConnection("server=localhost;database=testfiletable;uid=sa;pwd=123456;"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = $@"
-- 添加文件
declare @path hierarchyid;
SELECT @path=path_locator
FROM DocumentStore
WHERE name ='图片';

declare @new_path hierarchyid;

SELECT @new_path = @path.ToString() +
CONVERT (VARCHAR (20 ), CONVERT (BIGINT , SUBSTRING (CONVERT ( BINARY( 16), NEWID ()), 1 , 6))) + '.' +
CONVERT (VARCHAR (20 ), CONVERT (BIGINT , SUBSTRING (CONVERT ( BINARY( 16), NEWID ()), 7 , 6))) + '.' +
CONVERT (VARCHAR (20 ), CONVERT (BIGINT , SUBSTRING (CONVERT ( BINARY( 16), NEWID ()), 13 , 4))) + '/'

INSERT INTO DocumentStore (name , file_stream , path_locator ) values('海岸.jpg',@file_stream,@new_path);";
            var bs = File.ReadAllBytes(@"E:\test\filetable\海岸.jpg");
            SqlParameter param = new SqlParameter("file_stream", bs);
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

运行后,观察数据库：

再观察文件夹：

可以看到，已经通过c#代码将文件插入到了指定的目录下。

另外：通过sql语句插入文件夹很简单，这里就不再截图，可以参照下面语句：
INSERT INTO DocumentStore (name , is_directory )SELECT 'MyDir01' , 1 ;

2.2.6 关于FileTable的一些说明

从上面的实验中，我们也看到了，对于一个数据库来说，它有一个根路径（\\Uqianle\mssqlserver\FileStreamPath），对于每张FileTable表来说，它有一个二级路径（\\Uqianle\mssqlserver\FileStreamPath\DocumentStore），之后我们在这个FileTable中存储的内容，都会放到这个二级路径中去。
我们可以通过sql语句查看数据库、文件表以及文件的路径：

--查看数据库的FileStream根路径
SELECT FileTableRootPath();

--查询指定文件表二级路径
SELECT FileTableRootPath('DocumentStore');

--查询特定文件的路径
select name,is_directory, file_stream.GetFileNamespacePath() AS PATH,file_stream.GetFileNamespacePath(1,0) AS FULLPATH  FROM [DocumentStore]
1
2
3
4
5
6
7
8

输出如下：

2.3 FileTable在备份还原中的表现

备份之前，我们先观察下现有的数据量：



现在我们进行完整备份操作：


可以看到，有75M大小，很显然，这里和FileStream一样把FileTable中的文件也备份进来了。

然后，删除数据库testfiletable，删除后，观察文件夹：

现在来进行还原：

还原后，查看数据库和文件夹：



可以看到，FileTable和FileStream是一样的，也在备份还原中跟随备份介质。

2.4 FileTable的优缺点

FileTable相比FileStream的优点是：我们可以充分利用文件系统的api进行文档的管理工作，同时保留我们使用c#代码操作文档的能力。缺点就是固定了表结构，我们需要定义专门的表来管理文档。
另外，linux上是否支持此特性还有待实验。。。