inId,%207,%206)))%20+%20N'.'%20+%20%20%20%20%20%20%20%20%20%20%20%20%20CONVERT(nvarchar,%20CONVERT(bigint,%20SUBSTRING(@binId,%2013,%204)))%20+%20N'/'%20%20%20%20%20%20%20%20%20);%20%20%20%20%20RETURN%20@result;%20END;%20%20我们使用该函数插入同样的一个图片文件到该目录下：%20%20DECLARE%20@image1%20%20%20%20VARBINARY(MAX)%20--DECLARE%20@stream_id%20%20%20%20%20%20%20%20HIERARCHYID%20DECLARE%20@path_locator%20%20%20%20%20%20%20HIERARCHYID%20DECLARE%20@new_path%20%20%20%20VARCHAR(675)%20%20SELECT%20@image1%20=%20CAST(bulkcolumn%20AS%20VARBINARY(MAX))%20FROM%20OPENROWSET(BULK%20N'E:\Test\悲剧.jpg',%20SINGLE_BLOB)%20AS%20x%20%20SELECT%20@path_locator=path_locator%20FROM%20dbo.MyFileTable01%20WHERE%20name%20=%20'MyDir01'%20%20%20INSERT%20INTO%20dbo.MyFileTable01(name,%20file_stream,%20path_locator)%20SELECT%20N'悲剧.jpg',%20@image1,%20[dbo].[fnGetNewPathLocator](NEWID(),@path_locator)%20as%20NewPath%20%20%20select%20stream_id,file_stream,name,path_locator,parent_path_locator,is_directory%20from%20[dbo].MyFileTable01;%20%20如果想更进一步，让FileTable目录显示更加接近文件系统，可以将系统stream_Id字段替换为年+月+加标识符等，可以看这里的示例：%20http:/www.codeproject.com/Articles/584865/SQL-Server-FileTable-My-first-experience%20%20%20%20注意事项：%201、%20FileTable目录中不能存储15个级别的子目录，并且存储15个级别的子目录时，最下面的一级不能包含文件，因为这些文件将代表另外一个附加的级别。%20%202、%20NTFS%20文件系统支持远远超过Windows外壳程序和大多数Windows%20API的260个字符限制的路径名。因此，使用Transact-SQL在%20FileTable的文件层次结构中创建的文件有可能无法使用Windows资源管理器或很多其他Windows应用程序查看或打开，原因是这些文件的完整路径名称超过了260个字符。但是，您可使用%20Transact-SQL继续访问这些文件。%20%20%20关于SQL%20Server%202008中新增的原生分层结构数据hierarchyid，请看这里：%20http:/www.cnblogs.com/downmoon/archive/2011/05/03/2035259.html%20http:/msdn.microsoft.com/zh-cn/library/bb677173%28v=sql.100%29.
aspx%20http:/msdn.microsoft.com/zh-cn/library/bb677173.aspx%20http:/msdn.microsoft.com/en-us/magazine/cc794278.aspx#id0090037%20%20本文参考文章：%20http:/www.codeproject.com/Articles/584865/SQL-Server-FileTable-My-first-experience%20http:/www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/08/06/inserting-files-into-a-filetable-using-t-sql.aspx%20http:/www.cnblogs.com/zitjubiz/archive/2012/11/14/SQLSERVER_FileStream_FileTable.
html">http://technet.microsoft.com/zh-cn/library/ff929144.aspx
一、启用FileTable的先决条件
http://technet.microsoft.com/zh-cn/library/gg509097.aspx
USE master GO
EXEC sp_configure 'filestream access level',2
Go
RECONFIGURE GO
--查看实例级FileTable配置
EXEC sp_configure filestream_access_level;
GO
二、创建一个FileTable
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LearnFileTable')
DROP DATABASE LearnFileTable
GO
/****** 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\2012D