SQL Server 2012 Filetable directory instances using T-SQL operations

When SQL Server 2008 provides FILESTREAM to enhance SQL Server support for unstructured data with the Windows system's own APIs, SQL Server 2012 launches a Database like contained, Filetable and other exciting new features. There is no need to dwell on the features and features of filetable, this article is mainly for the filetable T-SQL Operation directory to do an example demo.

For an introduction to filetable, see msdn:http://technet.microsoft.com/zh-cn/library/ff929144.aspx

I. Prerequisites for enabling filetable

Http://technet.microsoft.com/zh-cn/library/gg509097.aspx

Use master go
EXEC sp_configure ' filestream access levels ', 2
go
reconfigure Go
--view instance-level filetable configuration
EXEC sp_configure filestream_access_level;
Go

Second, create a 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

Third, create filetable data table

Use learnfiletable
go
CREATE TABLE MyFileTable01 as  filetable
with
(
      filetable_directory =   ' MyFileTable01 ',
      Filetable_collate_filename = Database_default
);
Go
    
select * FROM [dbo]. MYFILETABLE01;