SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

    在SQL Server 2008提供FileStream,以借助Windows系统本身的API来强化SQL Server对于非结构化数据的支持后,SQL Server 2012更是推出了像Contained Database、FileTable等令人期待的新功能。对于FileTable的功能和特性,在此无需赘述,本文主要针对FileTable的T-SQL操作目录做一个实例演示。

关于FileTable的介绍,请参阅MSDN: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\2012Data\LearnFileTable_Log.ldf' ,
SIZE = 8128KB , MAXSIZE = 2097152KB , FILEGROWTH = 10%)
WITH FILESTREAM (NON_TRANSACTED_ACCESS   = FULL, DIRECTORY_NAME = N'LearnFileTable')
GO

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例


三、创建FileTable数据表

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

select * from [dbo].MyFileTable01;

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

注意,上图中的目录层次为:\\<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目录下:

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

重新查询:

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

注意:上图并没有根目录之外的常见文件目录结构,换句话说,都是位于根目录下的“平级”文件。如果要获取文件的完整路径,这需要结合使用 FileTableRootPath (Transact-SQL) 和 GetFileNamespacePath (Transact-SQL) 函数查看完整路径:

USE LearnFileTable;
DECLARE @root nvarchar(100);
DECLARE @fullpath nvarchar(1);

SELECT @root = FileTableRootPath();
SELECT @fullpath = @root + file_stream.GetFileNamespacePath()
    FROM [dbo].MyFileTable01
    WHERE name = N'DemoExcel.xlsx';

PRINT @fullpath;
GO

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

  除了手动创建目录、还可以通过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;

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

(2)插入一个空白文件到新目录:

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

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

如果你想使用SQL Server本身提供的hierarchyid层次结构,下面这个函数也许可以幫你:

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

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

SQL Server 2012新特性(1)TSQL操作FileTable目錄實例

如果想更进一步,让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

 

更多相关文章
  • 


    		    微軟SQL Server 2012新特性Silverlight報表客戶端  Power View
    微软SQL Server 2012是一个全面的数据库平台,使用集成的商业智能 (BI) 工具提供了企业级的数据管理.其中Reporting Service报表系统是目前最流行的报表设计,开发以及生成工具之一.日前微软透露,在SQL Server 2012中,将提供新的基于Silverlight技术平
  • 我们都知道,Sql Server在一个数据量巨大的表中添加一个非空栏位是比较费心的,缺乏经验的DBA或是开发人员甚至可能鲁莽地直接添加导致阻塞相应业务,甚至可能因为资源欠缺造成实例的全局问题.当然这都是Sql 2008R2及以前版本的情况.在SQL2012中采用了新的实现方式.这里我将对比相应的实现 ...
  • SQL SERVER 2005 与SQL SERVER 2008 新特性 SQLSERVER 2005 新特性 Enhanced Data Types:存储最大8K-2G Partitioned Table(分区表):数据拆分管理 SQL 2008 新特性 1).设置状态栏颜色 2).活动监视 DB ...
  • SQL Server 2014新特性——Buffer Pool擴展
    Buffer Pool扩展 Buffer Pool扩展是buffer pool 和非易失的SSD硬盘做连接.以SSD硬盘的特点来提高随机读性能. 缓冲池扩展优点 SQL Server读以随机读为主,SQL Server IO分为2部分:buffer pool管理方式,和buffer pool. SQ
  • SQL Server 2014新特性探秘(3)可更新列存儲聚集索引
    简介      列存储索引其实在在SQL Server 2012中就已经存在,但SQL Server 2012中只允许建立非聚集列索引,这意味着列索引是在原有的行存储索引之上的引用了底层的数据,因此会消耗更多的存储空间,但2012中的限制最大的还是一旦将非聚集列存储索引建立在某个表上时,该表将变为只
  • <MS SQL Server 2000管理员手册>系列--目录 Part I 简介 1. Microsoft SQL Server 2000 概观 . SQL Server 系统 . SQL Server 2000 的特性和新功能 . 本章总结 2. Microsoft Windows ...
  • SQL Server 2014新特性探秘(1)記憶體數據庫
    简介    SQL Server 2014提供了众多激动人心的新功能,但其中我想最让人期待的特性之一就要算内存数据库了.去年我再西雅图参加SQL PASS Summit 2012的开幕式时,微软就宣布了将在下一个SQL Server版本中附带代号为Hekaton的内存数据库引擎.现在随着2014CT ...
  • 


    		    10Windows Server 2012 新特性  DHCP策略
    在Windows的网络中,每一台计算机都需要有一个IP地址,这是不需要置疑的.而IP地址获
一周排行