在SQL Server 2008提供FileStream,以借助Windows系统本身的API来强化SQL Server对于非结构化数据的支持后，SQL Server 2012更是推出了像Contained Database、FileTable等令人期待的新功能。对于FileTable的功能和特性，在此无需赘述，本文主要针对FileTable的T-SQL操作目录做一个实例演示。
关于FileTable的介绍，请参阅MSDN:\
一、启用FileTable的先决条件
http://technet.microsoft.com/zh-cn/library/gg509097.aspx
USE master GOEXEC sp_configure 'filestream access level',2GoRECONFIGURE GO--查看实例级FileTable配置EXEC sp_configure filestream_access_level;GO
二、创建一个FileTable
USE masterGOIF EXISTS (SELECT name FROM sys.databases WHERE name = N'LearnFileTable') DROP DATABASE LearnFileTableGO/****** Object: Database [LearnFileTable] Script Date: 2014-04-23 9:25:32 ******/CREATE DATABASE [LearnFileTable] CONTAINMENT = NONE ON PRIMARY( NAME = N'LearnFileTable_Primary', FILENAME = N'E:\SQL2012Data\MyData\2012Data\LearnFileTable_Data.mdf' ,SIZE = 8128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [MyFS] CONTAINS FILESTREAM DEFAULT( NAME = N'LearnFileFS', FILENAME = N'E:\SQL2012Data\MyData\2012Data\LearnFileFS' , MAXSIZE = UNLIMITED) LOG ON( NAME = N'LearnFileTable_Log', FILENAME = N'E:\SQL2012Data\MyData\2012Data\LearnFileTable_Log.ldf' ,SIZE = 8128KB , MAXSIZE = 2097152KB , FILEGROWTH = 10%)WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'LearnFileTable')GO
USE LearnFileTableGOCREATE TABLE MyFileTable01 AS FileTableWITH( FileTable_Directory = 'MyFileTable01', FileTable_Collate_Filename = database_default);GOselect * from [dbo].MyFileTable01;
注意，上图中的目录层次为：\\<machine>\<instance-level FILESTREAM share>\<database-level directory>\<FileTable directory>\
（\\机器名\SQL实例名\FileTable数据库目录\FileTable目录名）
此目录层次结构构成了 FileTable 的文件命名空间的根。 在此目录层次结构下，FileTable 的 FILESTREAM 数据作为文件存储（包含文件和子目录的子目录）。
请务必记住：在此实例级别 FILESTREAM 共享区(即本实例中的“NET2012”)下创建的目录层次结构是虚拟目录层次结构。 该层次结构存储于 SQL Server 数据库中，并且在物理上不在NTFS文件系统中表示。 访问FILESTREAM 共享区之下和其包含的 FileTable 中的文件和目录的所有操作都将被文件系统中嵌入的 SQL Server 组件拦截和处理。
此时，我们可以手动添加几个文件到该FileTable目录下：
重新查询：
注意：上图并没有根目录之外的常见文件目录结构，换句话说，都是位于根目录下的“平级”文件。如果要获取文件的完整路径，这需要结合使用 FileTableRootPath (Transact-SQL) 和 GetFileNamespacePath (Transact-SQL) 函数查看完整路径：
USE LearnFileTable;DECLARE @root nvarchar(100);DECLARE @fullpath nvarchar(1000);SELECT @root = FileTableRootPath();SELECT @fullpath = @root + file_stream.GetFileNamespacePath() FROM [dbo].MyFileTable01 WHERE name = N'DemoExcel.xlsx';PRINT @fullpath;GO
除了手动创建目录、还可以通过T-SQL创建FileTable目录。
四、通过T-SQL创建FileTable目录
这个过程我们分两步：
（1）创建一个新目录
INSERT INTO dbo.MyFileTable01(name, is_directory)SELECT 'MyDir01', 1;select stream_id,file_stream,name,path_locator,is_directory from [dbo].MyFileTable01;
（2）插入一个空白文件到新目录：
DECLARE @path HIERARCHYIDDECLARE @new_path VARCHAR(675)SELECT @path = path_locatorFROM dbo.MyFileTable01WHERE name = 'MyDir01'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.MyFileTable01(name, file_stream, path_locator)SELECT N'空文件.txt', 0x, @new_path--select stream_id,file_stream,name,path_locator,is_directory from [dbo].MyFileTable01
如果你想使用SQL Server本身提供的hierarchyid层次结构，下面这个函数也许可以帮你：
CREATE FUNCTION [dbo].[fnGetNewPathLocator] (@child uniqueidentifier ,@parent hierarchyid = NULL)RETURNS hierarchyidASBEGIN DECLARE @result hierarchyid, @binId binary(16) = CONVERT(binary(16), @child); SELECT @result = hierarchyid::Parse ( COALESCE(@parent.ToString(), N'/') + CONVERT(nvarchar, CONVERT(bigint, SUBSTRING(@binId, 1, 6))) + N'.' + CONVERT(nvarchar, CONVERT(bigint, SUBSTRING(@binId, 7, 6))) + N'.' + CONVERT(nvarchar, CONVERT(bigint, SUBSTRING(@binId, 13, 4))) + N'/' ); RETURN @result;END;
我们使用该函数插入一个图片文件到该目录下：
DECLARE @image1 VARBINARY(MAX)--DECLARE @stream_id HIERARCHYIDDECLARE @path_locator HIERARCHYIDSELECT @image1 = CAST(bulkcolumn AS VARBINARY(MAX))FROM OPENROWSET(BULK N'E:\Test\悲剧.jpg', SINGLE_BLOB) AS xSELECT @path_locator=path_locator FROM dbo.MyFileTable01 WHERE name = 'MyDir01'INSERT INTO dbo.MyFileTable01(name, file_stream, path_locator)SELECT N'悲剧.jpg', @image1, [dbo].[fnGetNewPathLocator](NEWID(),@path_locator) as NewPathselect stream_id,file_stream,name,path_locator,parent_path_locator,is_directory from [dbo].MyFileTable01;
注意：上面两种方法中，一个path_locator为Varchar，一个为HIERARCHYID。
如果想更进一步，让FileTable目录显示更加接近文件系统，可以将系统stream_Id字段替换为年+月+加标识符等，可以看这里的示例：
http://www.codeproject.com/Articles/584865/SQL-Server-FileTable-My-first-experience
特别限制：
1、 FileTable目录中不能存储15个级别的子目录，并且存储15个级别的子目录时，最下面的一级不能包含文件，因为这些文件将代表另外一个附加的级别。
2、 NTFS 文件系统支持远远超过Windows外壳程序和大多数Windows API的260个字符限制的路径名。因此，使用Transact-SQL在 FileTable的文件层次结构中创建的文件有可能无法使用Windows资源管理器或很多其他Windows应用程序查看或打开，原因是这些文件的完整路径名称超过了260个字符。但是，您可使用 Transact-SQL继续访问这些文件。
关于SQL Server 2008中新增的原生分层结构数据hierarchyid，请看这里：
http://www.cnblogs.com/downmoon/archive/2011/05/03/2035259.html
http://msdn.microsoft.com/zh-cn/library/bb677173.aspx
本文参考文章：
http://www.codeproject.com/Articles/584865/SQL-Server-FileTable-My-first-experience
http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/08/06/inserting-files-into-a-filetable-using-t-sql.aspx
http://www.cnblogs.com/zitjubiz/archive/2012/11/14/SQLSERVER_FileStream_FileTable.html
邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! [email protected]