FileTable文件表的使用

sqlsever2012以后才开始支持FileTable

用户可以通过 Windows 资源管理器来访问 FileTable 目录，并通过拖拽文件的方式来把它们添加到数据库中。FileTable 会存储文件的名称和类型、路径、语言、创建和修改的时间以及其他状态信息。它还会从根目录开始维护整个目录的结构。尽管 FileTable 目录和所有其它 Windows 的目录行为一样，但是物理文件实际上是存储在 SQL Server 中，而不是文件系统中。

1、修改数据库引擎的属性

打开“SQL Server配置管理器”，设置启用 FILESTREAM。
（1）针对 Transact-SQL 访问启用 FILESTREAM
　　选中此项可针对 Transact-SQL 访问启用 FILESTREAM。 必须选中此控制选项，才能使用其他控制选项。如果不启用此选项，就不能添加FileStream文件组。

（2）针对文件 I/O 流访问启用 FILESTREAM
　　选中此项可针对 FILESTREAM 启用 Win32 流访问。

（3）Windows 共享名
　　使用此控制选项可输入将用来存储 FILESTREAM 数据的 Windows 共享的名称。默认为该SQL Server实例的名称。

（4）允许远程客户端针对 FILESTREAM 数据启用流访问
　　选中此控制选项可允许远程客户端访问此服务器上的此 FILESTREAM 数据。

2、修改服务器的属性

打开“SQL Server Management Studio”，右键“服务器实例“-”属性”-“高级”-“FILESTREAM 访问级别”

（1）已禁用 即filestream_access_level =0

（2）已启用 Transact-SQL 访问 即filestream_access_level =1

（3）已启用完全访问 即filestream_access_level =2

注意：在首次启用 FILESTREAM 时，您可能需要重新启动计算机才能配置驱动程序。

EXEC sys.sp_configure N’filestream_access_level’, N’2’
RECONFIGURE WITH OVERRIDE
注意：设置完成之后，重启实例。

3、配置防火墙

若要在防火墙保护的环境中使用 FILESTREAM，客户端和服务器都必须能够将 DNS 名称解析为包含 FILESTREAM 文件的服务器。FILESTREAM 要求 Windows 文件共享端口 139 和 445 处于打开状态。

二、配置文件组

1、添加文件组

ALTER DATABASE [dbfile] ADD FILEGROUP [FileStreamFileGroup] CONTAINS FILESTREAM

2、添加文件(在这里因为逻辑名dbfile已经被数据库占用，所以使用了FileData作为目录名)
ALTER DATABASE [dbfile] ADD FILE ( NAME = N’ImageTable1’, FILENAME = N’D:\mssql\FileData\ImageTable1’ ) TO FILEGROUP [FileStreamFileGroup]
注意：在运行脚本之前，必须存在D:\mssql\FileData，
同时不能存在D:\mssql\FileData\ImageTable1。否则也会报错：

3、启动非事务访问
数据库dbfile右键“属性”-“选项”-“ILESTREAM 非事务访问”
1）FILESTREAM 非事务访问
– OFF关闭后,路径无法打开,此时文件表 [ImageTable1] 仍可正常操作
– READ_ONLY只读状态,路径可拷贝文件出来,但无法拷贝文件到该目录,此时文件表 [ImageTable1] 仍可正常操作
– FULL完全开启,此时目录可以正常操作，文件表也可以正常操作

（2）FILESTREAM 目录名称
　　为FILESTREAM指定目录名称。该目录名称级别为: FILESTREAM指定的共享名称的子级，FileTable的父级

如果启用非事务性访问时没有提供目录名称，则在以后必须提供它，这样才能在数据库中创建 FileTable。

如果是通过脚本执行，如下：
ALTER DATABASE dbfile
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’dbfile’ )

说明：为检查是否在数据库上启用了非事务性访问，可以查询目录视图：
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options

三、创建FileTable

1、创建第一个FileTable (推荐这种使用文件组的方式创建FileTable)

CREATE TABLE [dbo].[ImageTable1] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamFileGroup]
WITH (
FILETABLE_DIRECTORY = N’ImageTable1’,
FILETABLE_COLLATE_FILENAME = Chinese_PRC_CI_AS
);

2、创建一个新的 FileTable (不推荐这种没有文件组的方式创建FileTable)
CREATE TABLE ImageTable2 AS FileTable
WITH (
FileTable_Directory = ‘ImageTable2’,
FileTable_Collate_Filename = database_default
);
GO

3.获取共享路径

文件表创建之后，就会相应的产生一个文件表共享目录，该目录路径可以通过内建函数获取：

– returns “\MyPC\MSSQLSERVER\dbfile”
SELECT FileTableRootPath();

– returns “\MyPC\MSSQLSERVER\dbfile\ImageTable1”
SELECT FileTableRootPath(N’dbo.ImageTable1’);

– returns “\MyPC\MSSQLSERVER\dbfile\ImageTable2”
SELECT FileTableRootPath(N’ImageTable2’);

4.查看

通过Windows资源管理器，可见已经创建了以GUID命名的目录。

6.修改表名（修改表名时最好同时修改对应的目录名称，以保持表名与目录名称一致）
EXEC sp_rename 'ImageTable1’, ‘ImageTable3’;

5.更改 FileTable 的目录
ALTER TABLE ImageTable1
SET ( FILETABLE_DIRECTORY = N’ImageTable3’ );
GO

5.删除 FileTable
可以使用 DROP TABLE (Transact-SQL) 语句的普通语法删除 FileTable。
当您删除 FileTable 时，同时也会删除对应的文件。

四、操作
共享目录下修改文件的名字、增加文件的内容、更改文件的类型等都会自动地在数据库的ImageTable1下做相应的更改。
数据库中可以修改文件名，删除文件，其他操作如添加，复制，修改内容等均不支持。

1、向目录中添加文件

通过 “Windows资源管理器”，向目录\MyPC\MSSQLSERVER\dbfile\ImageTable1 中添加一个文件A01.GIF。

然后运行脚本：
select * from ImageTable1
可见， SQL Server自动在Table中添加了记录。

FileTable 上的 INSERT 操作
下列注意事项适用于 FileTable 上的 INSERT 操作：
所有文件属性列具有 NOT NULL 约束。 如果没有显式设置值，则提供适当的默认值。
如果 INSERT 语句设置了 name、 path_locator、 parent_path_locator或文件属性，则强制执行系统定义的约束。
该应用程序可以通过提供指向 GetPathLocator (Transact-SQL) 函数的文件系统路径，来获取文件或目录的 path_locator。

–添加目录

INSERT INTO dbo. ImageTable1 (name , is_directory )
SELECT ‘MyDir01’ , 1 ;

–添加文件
declare @path hierarchyid;
SELECT @path=path_locator
FROM dbo. ImageTable1
WHERE name = ‘MyDir01’;

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 dbo. ImageTable1 (name , file_stream , path_locator )
SELECT N’空文件.txt’ , 0x , @new_path;

–复制文件
–复制“\MyDir01\空文件.txt”到MyDir01目录中
insert into imagetable1
select file_stream,getpathlocator(FileTableRootPath(’[dbo].[ImageTable1]’)+’\MyDir01’).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))) + ‘/’
,‘空文件4.txt’
from [imagetable1]
where path_locator= getpathlocator(FileTableRootPath(’[dbo].[ImageTable1]’)+’\MyDir01\空文件.txt’ )
;

2、FileTable 上的 UPDATE 操作
–修改文件名（文件名必须符合windows命名要求）
update ImageTable1 set name=‘Cup.GIF’ where name=‘A01.GIF’

–修改文件内容
可以对 file_stream 列中的 FILESTREAM 数据进行更新，且不会影响任何其他列（包括时间戳）。

update ImageTable1 set file_stream=CAST(‘Xray 1’ as varbinary(max))
where path_locator= getpathlocator ( FileTableRootPath(’[dbo].[ImageTable1]’)++’\MyDir01\空文件.txt’ )

3、删除文件
Delete ImageTable1 where name=‘Cup.GIF’

或者，通过“Windows资源管理器”直接删除该文件。

4.查看文件路径
– returns “\ImageTable1\hello.txt"
select file_stream.GetFileNamespacePath() AS PATH FROM ImageTable1

–returns “\MyPC\MSSQLSERVER\dbfile\ImageTable1\MyDir01\空文件.txt”
select file_stream.GetFileNamespacePath(1, 0) from ImageTable1

5.根据文件路径查看文件id
select GetPathLocator(FileTableRootPath(’[dbo].[ImageTable1]’)+’\hello.txt’) from ImageTable1

五、备份和还原
　　使用 SQL Server 备份数据库时，FILESTREAM 数据将与数据库中的结构化数据一起备份。
如果不想将 FILESTREAM 数据与关系数据一起备份，则可以使用部分备份将 FILESTREAM 文件组排除在外。

六、管理filetable

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

SELECT * FROM sys.filetables;

或

SELECT * FROM sys.tables WHERE is_filetable = 1;

2、禁用和重新启用表级别 FileTable 命名空间
禁用 FileTable 命名空间将会禁用所有系统定义的约束并触发使用 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表，选择“浏览 FileTable 目录”即可