sqlserver关于filestream文件流、filetable文件表的总结

个人理解：

FileStream、Filetable和Oracle的外部表有点像，但又不完全像，相似点都是表中的实际数据存放在文件系统中，表只记录元数据。

Oracle外部表的数据存放在文件系统中,使用directory name和数据库联系在一起,oracle外部表是只读表

FileStream的表的filestream字段的数据实际上存放在文件系统中,使用数据库FILESTREAM文件组对应的目录和文件系统联系在一起,也就是所谓的"数据容器",是可读写的表

Filetable表的数据,个人理解实际上也是存放在文件系统中,它是基于FileStream基础上来的，不仅使用数据库FILESTREAM文件组对应的目录和文件系统联系在一起, 也使用数据库Directory名称和文件系统联系在一起,这样从Windows应用程序访问它们，就好像它们存储在文件系统中，而不必对您的客户端应用程序进行任何更改，比如使用操作系统、 命令提示符、批处理文件、C# 或 Visual Basic.NET的接口System.IO的新建粘贴复制删除等直接往\\testmachine\MSSQLSERVER\filestream_testdb\table_1里面添加更新删除文件即完成了对filetable的dml操作。是可读写的表

一些实验结论

1、在filetable文件表中对应的文件系统文件，可以在操作系统中删除，但是不能修改，比如123.txt文件，可以删除，但是无法使用notepad打开进行修改，notepad窗口显示the request is not support

2、以上1的notepad窗口不关闭，filetable文件表对应的数据库可以正常进行sqlsderver的backup备份，且可以正常restore恢复，恢复后，可以在恢复后的数据库对应的文件系统中看到123.txt文件

3、filetable1和filetable2都是文件系统表时，无法执行insert into filetable1 select * from filetable2这样的操作，会报错Column name or number of supplied values does not match table definition.

4、filetable文件表的数据库进行备份后，在异机恢复后，异机的文件系统里自动有了filetable对应的文件，比如filetable文件表的数据库对应的文件系统在pc1的路径下有文件\\PC1\MSSQL\dir1\table1,恢复到异机后,异机自动有了\\异机\MSSQL\dir1\table1

5、SELECT * into table1 from filetable1;其中filetable1是filetable文件表，但是table1就是普通表而非filetable文件表，也就是说FileTable 中的 SELECT INTO 语句将不在创建的目标表上传播 FileTable 语义(就像常规表中的 FILESTREAM 列一样)。 所有目标表列的行为就像常规列的行为一样。

6、FileTable 不支持分区。

7、FileStream、FileTable的数据库无法搭建mirror，会报错

A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage.

8、always on时FileStream、FileTable的FILESTREAM 文件组不需要位于共享磁盘资源

9、FileTable有两个DIRECTORY_NAME，一个是数据库级的，一个是表级的。

10、仅FileStream没有Filetable时，对应文件系统目录就是FILESTREAM文件组对应的目录，即数据容器

11、Filetable文件表，对应文件系统目录有两个

11.1、\\SERVERNAME\FILESTREAM_SHARE_NAME\FILESTREAM_DIRECTORY_NAME\FILETABLE_DIRECTORY

11.2、FILESTREAM文件组对应的目录，即数据容器

12、FileTable对应文件系统目录假如是\\testmachine\MSSQLSERVER\filestream_testdb\table_1时，在这个目录手工新增一个操作系统文件比如test.txt后，再查询select * from testdb.dbo.table1就可以看到一条新增的记录了，执行delete from testdb.dbo.table1后，发现刚刚新增的文件不存在了

13、FileTable文件表对应的文件系统目录结构为\\SERVERNAME\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY,实例目录、数据库目录、表目录分别参见SERVERPROPERTY('FilestreamShareName')、sys.database_filestream_options、sys.filetables

FileStream

官方文档https://docs.microsoft.com/zh-cn/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017

在SQL Server中，BLOB可以是将数据存储在表中的标准varbinary(max)数据，也可以是将数据存储在文件系统中的FILESTREAM varbinary(max)对象。FILESTREAM 通过将SQL Server数据库引擎varbinary(max)二进制大型对象(BLOB)数据作为文件存储在NTFS或ReFS文件系统中，将与该文件系统集成在一起。 Transact-SQL语句可插入、更新、查询、搜索和备份FILESTREAM数据。FILESTREAM 存储以varbinary(max)列的形式实现，在该列中数据以BLOB的形式存储在文件系统中。 BLOB的大小仅受文件系统容量大小的限制。文件大小为2GB 的varbinary(max)标准限制不适用于存储在文件系统中的BLOB。若要指定列应将数据存储在文件系统中，请对varbinary(max)列指定FILESTREAM属性。 这样数据库引擎会将该列的所有数据存储在文件系统，而不是数据库文件中。FILESTREAM 数据必须存储在FILESTREAM文件组中。 FILESTREAM文件组是包含文件系统目录而非文件本身的专用文件组。这些文件系统目录称为“数据容器” 。 数据容器是 数据库引擎存储 与 文件系统存储 之间的接口,可以将多个数据容器添加到 FILESTREAM 文件组。

开启FileStream 功能

1. 打开 SQL Server 配置管理器， 在SQL server 服务下找到你要启用的SQL Server服务(默认实例一般是 MSSQLServer)，右击该服务，选择属性，在属性窗口你可以看到FileStream 标签，选中” 针对 Transact-SQL 访问启用 FILESTREAM"

2.执行以下命令，或右键实例--properties--advanced--filestream，选择full access enabled

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

3.对数据库test增加一个FILESTREAM类型的filegroup组filestream1

ALTER DATABASE [test] ADD FILEGROUP [filestream1] CONTAINS FILESTREAM

4、对数据库test增加一个FILESTREAM类型的file文件file_stream1,filename的值G:\DEFAULT.DATA\file_filestream1是目录非文件，且父目录G:\DEFAULT.DATA必须先存在，子目录file_filestream1会自动创建(子目录不能先存在否则会报错目录已经存在导致无法创建子目录)。

ALTER DATABASE test

add FILE (name = 'file_stream1',FILENAME = 'G:\DEFAULT.DATA\file_filestream1') TO FILEGROUP filestream1

5、

5.1、创建普通表使用FILESTREAM

CREATE TABLE table1

(

Id INT NOT NULL PRIMARY KEY,

Photo VARBINARY(MAX) FILESTREAM NULL,

RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

UNIQUE DEFAULT NEWID()

)

G:\DEFAULT.DATA\file_filestream1目录下新增了一个目录假设为1，且子目录1下面还自动生成了一个子目录，假设为1_1

5.2、创建基于FILESTREAM的filetable文件表

ALTER DATABASE [test] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'test');

--设置filestream的完全非事务性访问，且必须设置数据库级别的directory名称

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON filestream1 WITH

(

FILETABLE_DIRECTORY = N'table2', FILETABLE_COLLATE_FILENAME = database_default

)

其中FILETABLE_COLLATE_FILENAME表示排序规则，值可以为database_default或SQL_Latin1_General_CP1_CI_AS等等

G:\DEFAULT.DATA\file_filestream1目录下又新增了一个目录假设为2，且子目录2下面还自动生成了一个子目录，假设为2_1

6、往上面5例子中的table1表插入数据

INSERT INTO test.dbo.table1 VALUES ( 1, NULL,newid ());

G:\DEFAULT.DATA\file_filestream1\2\2_1下没有文件

INSERT INTO test.dbo.table1 VALUES ( 3, CAST('Seismic Data' as varbinary(max)),newid ());

G:\DEFAULT.DATA\file_filestream1\2\2_1下生成了一个文件00000022-00000088-0002

7、更新table1表的记录

update test.dbo.table1 set photo=CAST('Xray 1' as varbinary(max)) where id=3

G:\DEFAULT.DATA\file_filestream1\2\2_1下生成了一个文件00000022-000000c5-0005

8、删除table1表的记录

delete from test.dbo.table1

G:\DEFAULT.DATA\file_filestream1\2\2_1下面的两个文件还在

使用 FILESTREAM 存储时，请考虑以下内容：

如果表包含 FILESTREAM 列，则每一行都必须具有唯一的一个非 Null 行 ID。

不能嵌套 FILESTREAM 数据容器。

使用故障转移群集时，FILESTREAM 文件组必须位于共享磁盘资源上。个人已实验过，always on时FILESTREAM 文件组不需要位于共享磁盘资源

FileTable

官方文档https://docs.microsoft.com/zh-cn/sql/relational-databases/blob/filetables-sql-server?view=sql-server-2017

FileTable是基于SQL Server 2008的FILESTREAM特性上而来的,允许我们把Windows文件存储在SQL Server中，也就是说可以在SQL Server中将文件和文档存储在称作FileTable的特别的表中，但是从Windows应用程序访问它们，就好像它们存储在文件系统中，而不必对您的客户端应用程序进行任何更改。而且这些FileTable表又可以使用SQL Server进行备份和恢复，在文件系统中对文件的创建更新删除都会反映到FILETABLE表中，对FILETABLE表执行DML操作，这些改动同样会反映到文件本身的文件系统属性，FILETABLE表中的每一行数据代表了一个文件，表中的栏位代表了文件的一些属性列，像文件创建时间和修改时间，以及文件的路径。FileTable 功能使企业客户能够在 SQL Server 数据库中存储非结构化文件数据和目录层次结构。

FileTable关于事务性和非事务性的问题，FileTable作为一张SQL Server表是支持事务性的，但是由于对文件的操作是属于一个Windows文件系统的操作行为，是不具备事务性的，所以也可以在数据库级别配置FILESTREAM数据的非事务性访问级别如NON_TRANSACTED_ACCESS = FULL。但是不代表说FileStream本身是必须开启这个选项的。也就是如果我不使用FileTable，我再添加FILESTREAM FILEGROUP的时候是不需要启用non-transactional access选项。

创建filetable文件表

开启FILESTREAM功能并创建好了FILESTREAM的file和filegroup后，再配置一下NON_TRANSACTED_ACCESS、DIRECTORY_NAME就可以创建filetable了

ALTER DATABASE [testdb] ADD FILEGROUP [file_stream1] CONTAINS FILESTREAM

ALTER DATABASE [testdb] ADD FILE (name = 'filestream001',FILENAME = 'G:\DEFAULT\filestream1') TO FILEGROUP file_stream1

ALTER DATABASE [testdb] SET FILESTREAM(NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'filestream_testdb');

CREATE TABLE table1 AS FILETABLE FILESTREAM_ON file_stream1

WITH

(

FILETABLE_DIRECTORY = N'table_1', FILETABLE_COLLATE_FILENAME = database_default

)

创建完表之后你可以通过Windows Explorer在FileStream共享路径下新建、删除、修改文件，共享路径为

\\SERVERNAME\FILESTREAM_SHARE_NAME\FILESTREAM_DIRECTORY_NAME\FILETABLE_DIRECTORY，即进入\\testmachine\MSSQLSERVER\filestream_testdb\table_1目录

FILESTREAM_SHARE_NAME：见SSMS-->右键实例-->Properties-->Advanced-->FILESTREAM-->FILESTREAM SHARE NAME,默认是MSSQLSERVER

FILESTREAM_DIRECTORY_NAME：见SSMS-->右键数据库-->Properties-->Options-->FILESTREAM-->FILESTREAM Directory Name

例如filetable表名testdb.dbo.table1

1、对应文件系统目录就是G:\DEFAULT.DATA\filefilestream1\1\1_1,首先FILESTREAM文件组目录G:\DEFAULT.DATA\filefilestream1\目录，table1表创建后，文件组目录下新增了一个目录假设为1，且子目录1下面还自动生成了一个子目录，假设为1_1

2、对应文件系统目录是\\testmachine\MSSQLSERVER\filestream_testdb\table_1，在这个目录手工新增一个操作系统文件比如test.txt后，再查询select * from testdb.dbo.table1就可以看到一条新增的记录了，执行delete from testdb.dbo.table1后，发现刚刚新增的文件不存在了

3、\\testmachine\MSSQLSERVER\filestream_testdb\table_1里面每新增1个文件，G:\DEFAULT.DATA\filefilestream1\1\1_1下面新增2个文件

4、\\testmachine\MSSQLSERVER\filestream_testdb\table_1删除那个新增的文件后，G:\DEFAULT.DATA\filefilestream1\1\1_1下面的2个文件还在

将文件加载到 FileTable 中的方法

1、在 Windows 资源管理器中将文件从源文件夹拖放到新的 FileTable 文件夹。

2、从命令提示符下、批处理文件或脚本中使用命令行选项(如 MOVE、COPY、XCOPY 或 ROBOCOPY)。

3、用 C# 或 Visual Basic.NET 编写一个用于移动或复制文件的自定义应用程序。 从 System.IO 命名空间调用方法。

管理filetable

1、查询哪些表是filetable文件表

SELECT * FROM sys.filetables;

或

SELECT * FROM sys.tables WHERE is_filetable = 1;

2、禁用和重新启用表级别 FileTable 命名空间

ALTER TABLE filetable_name ENABLE|DISABLE FILETABLE_NAMESPACE;

3、终止与 FileTable 关联的打开的文件句柄(FileTable 中存储的文件的打开句柄可以阻止某些管理任务所需的独占访问。 若要启用紧急任务，您可能要终止与一个或多个 FileTable 关联的打开的文件句柄)

3.1、与 FileTable 关联的打开的文件句柄的列表

SELECT * FROM sys.dm_filestream_non_transacted_handles;

3.2、终止与 FileTable 关联的打开的文件句柄

USE database_name;

-- Kill all open handles in all the filetables in the database.

EXEC sp_kill_filestream_non_transacted_handles;

-- Kill all open handles in a single filetable.

EXEC sp_kill_filestream_non_transacted_handles @table_name = 'filetable_name';

-- Kill a single handle.

EXEC sp_kill_filestream_non_transacted_handles @handle_id = integer_handle_id;

4、查询实例的FILESTREAM 使用的DIRECTORY_NAME
SELECT  SERVERPROPERTY('FilestreamShareName')

5、查询FILETABLE表的数据库对应的DIRECTORY_NAME

select db_name(database_id),* from sys.database_filestream_options

仅仅使用filestream功能时，数据库不需要对应的DIRECTORY_NAME

6、查询FILETABLE表对应的DIRECTORY_NAME

select object_name(object_id),* from sys.filetables

7、查询filetable表testdb.dbo.table1中的文件完整路径名称

SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1

8、查询FILETABLE表对应的文件系统目录，右键FILETABLE表，选择Explore FileTable Directory即可

关于表级别的FILETABLE_DIRECTORY的说明

FileTable表的FILETABLE_DIRECTORY在建立表的时候会自动创建，如果建表时没有指定FILETABLE_DIRECTORY则FILETABLE_DIRECTORY等于表名。不同的FileTable表必须使用不同FILETABLE_DIRECTORY目录，一个FILETABLE_DIRECTORY目录不能被两张不同的表使用

1、

ALTER DATABASE [testdb] SET FILESTREAM(NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'testdb');

--数据库的DIRECTORY_NAME目录名称为testdb，见SSMS-->右键数据库-->Properties-->Options-->FILESTREAM-->FILESTREAM Directory Name

绝对路径：\\testmachine\MSSQLSERVER\testdb\

2、

CREATE TABLE table1 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = N'table1', FILETABLE_COLLATE_FILENAME = database_default

)

--表table1的FILETABLE_DIRECTORY目录名称为table1

绝对路径：\\testmachine\MSSQLSERVER\testdb\table1

3、

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = N'table1', FILETABLE_COLLATE_FILENAME = database_default

)

--报错如下，因为FILETABLE_DIRECTORY 目录名称'table1'已经被表table1使用了，不能再给表table2使用

FILETABLE_DIRECTORY 'table1' attempting to be set on table 'table2' is not unique in the database 'file_db'. Provide a unique value for the option FILETABLE_DIRECTORY to this operation.

4、

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = N'table99', FILETABLE_COLLATE_FILENAME = database_default

)

--表table2的FILETABLE_DIRECTORY目录名称是table99

绝对路径：\\testmachine\MSSQLSERVER\testdb\table99

5、

CREATE TABLE table3 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_COLLATE_FILENAME = database_default

)

--表table3的FILETABLE_DIRECTORY目录名称默认是table3

绝对路径：\\testmachine\MSSQLSERVER\testdb\table3

Filetable遇到过的报错

1、备份报错

The operating system returned the error '5(Access is denied.)' while attempting 'CreateFile' on 'T:\DEFAULT.FILESTREAM.DATA\Netapp1_9_FS2\ee50c020-b8df-485b-b7fd-924123a9f8e5\1dafa2ac-3d5e-4b8e-b4c5-711ded06ae19\00001526-000034f6-0002'.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

2、恢复报错(备份包是正常备份的)

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'OpenFile' on 'F:\FS2.FILESTREAM.DATA\Netapp2_1_FS5\0bb73706-bf4a-4d2e-84bb-fa714c2ba93a\d6c88672-71bf-40b3-9229-667528207588\00000352-0003d60d-0015'.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

3、恢复报错(备份包是正常备份的)

A previous restore operation was interrupted and did not complete processing on file 'AMPFileStream'. Either restore the backup set that was interrupted or restart the restore sequence.

4、\\SERVERNAME\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY正常打开，有一次重启服务器后，无法打开了，检查了数据库一切正常，在该服务器内使用\\127.0.0.1\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY可以正常打开，使用\\SERVERNAME\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY或\\该服务器IP\INSTANCE_DIRECTORY\DB_DIRECTORY\TABLE_DIRECTORY都无法打开，想到应该是远程访问有问题，重置filestream功能即可，在Sql Server Configuration Manager--Sql Server Services--右键对应实例--Properties--FILESTREAM