SQL Server的FileStream和FileTable深入剖析

USE master GO
EXEC sp_configure 'filestream access level', 2
Go
RECONFIGURE GO
--You can use this statement to see current
--config value and running value
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
CREATE DATABASE LearnFileTable
--Details of primary file group
ON PRIMARY
( NAME = LearnFileTable_Primary,
FILENAME =N'D:\FileTable\LearnFileTable_Data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
--Details of additional filegroup to be used to store data
FILEGROUP DataGroup
( NAME = LearnFileTable_Data,
FILENAME =N'D:\FileTable\LearnFileTable_Data.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
--Details of special filegroup to be used to store FILESTREAM data
FILEGROUP FSDataGroup CONTAINS FILESTREAM
( NAME = FileStream,
--FILENAME refers to the path and not to the actual file name. It
--creates a folder which contains a filestream.hdr file and
--also a folder $FSLOG folder as depicted in image below
FILENAME =N'D:\FileTable\FSData')
--Details of log file
LOG ON
(Name = LearnFileTable_Log,
FILENAME = 'D:\FileTable\LearnFileTable_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'LearnFileTable')
--Other option for NON_TRANSACTED_ACCESS is READ_ONLY or OFF
GO

-- Check the Filestream/FileTable Options
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc, directory_name FROM sys.database_filestream_options
WHERE DB_NAME(database_id) = 'LearnFileTable'

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