SQL Server 2012新特性（一）T-SQL操作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

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\2012Data\LearnFileTable_Log.ldf' ,
SIZE = 8128KB , MAXSIZE = 2097152KB , FILEGROWTH = 10%)
WITH FILESTREAM (NON_TRANSACTED_ACCESS   = FULL, DIRECTORY_NAME = N'LearnFileTable')
GO

USE LearnFileTable
GO
CREATE TABLE MyFileTable01  AS FileTable
WITH
(
      FileTable_Directory =   'MyFileTable01',
      FileTable_Collate_Filename = database_default
);
GO

select * 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;

DECLARE @path        HIERARCHYID
DECLARE @new_path    VARCHAR(675)

SELECT @path = path_locator
FROM dbo.MyFileTable01
WHERE 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

CREATE FUNCTION [dbo].[fnGetNewPathLocator]
    (@child uniqueidentifier
    ,@parent hierarchyid = NULL)
RETURNS    hierarchyid
AS
BEGIN
    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        HIERARCHYID
DECLARE @path_locator       HIERARCHYID

SELECT @image1 = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK N'E:\Test\悲剧.jpg', SINGLE_BLOB) AS x

SELECT @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 NewPath

select stream_id,file_stream,name,path_locator,parent_path_locator,is_directory from [dbo].MyFileTable01;

注意：上面两种方法中，一个path_locator为Varchar，一个为HIERARCHYID。

邀月注：本文版权由邀月和****共同所有，转载请注明出处。
助人等于自助!   3w@live.cn