filetable特性

/*
互联网时代数据是爆炸式增长，我们常常需要把结构化数据和非结构化数据（如文档，演示文稿，视频，音频，图像）存储在一起。通常有几种方案：

1。在数据库中存储结构化数据，在文件系统中存储非结构化数据，然后数据库里有一个字段记录文件系统的路径，虽然这种方法成本合算，但它引入了额外的复杂度，因为你需要手动去保证跨关系和非关系系统管理事务的完整性。
2。将结构化数据和非结构化数据都存储在数据库中，多年以来，数据库一直都支持存储非关系数据，如二进制大对象，或BLOB，SQL Server称之为varbinary数据类型，这样的好处是能充分利用数据库的特性，但成本费用会更高，所需的磁盘空间更多，存储和检索时间更长，对应用程序的整体性能也会有负面影响。
3。SQL Server 2008中引入的FILESTREAM（文件流）特性是在现有的varbinary(max)数据类型之上实现的，你可以在服务器的NTFS文件系统上存储真实的数据，数据库中存储的是文件系统上的一个指针。可以在数据库上下文内管理和访问。自动保证了事务的完整性。
事务的一致性。
SQLServer集成的安全模型。
备份和恢复时，会包含FILESTREAM BLOB数据。
支持全文搜索。
用SELECT，INSERT，UPDATE和DELETE语句，操作带FILESTREAM数据的表，性能没有变慢。

4。SQL Server 2012的FileTable则进一步增强， FileTable使用FILESTREAM的基础上进一步加强，它既允许直接的，事务性的，存取FILESTREAM列大型数据。FileTable表也可以配置为允许非事务性访问文件，而无需事先SQLServer授权。

FileTable是一种特殊类型的表，它的结构是固定的，不像普通用户表可以定义自己的字段，就好像它是一个文件夹中的文件系统。一个的FileTable包含的FileStream随着几个文件级属性（file_id，名称，路径，创建日期，修改日期，最后访问时间等），文件和目录层次结构的数据。这意味着FileTable中的每一行数据代表一个文件系统上的文件或目录。
FileTable在文件系统表现为一个windows共享目录，里面有文件和目录数据，你可以通过非事务性的文件访问（基于Windows API的应用程序能够访问文件，而无需SQLServer访问权限）。对于Windows应用程序，这看起来像一个正常的网络共享位置的文件和目录。应用程序可以使用Windows API来管理这个网络共享位置的文件和目录。

Windows API操作是非事务性的，和数据库是不相关。然而，FileTable是基于FileStream实现的，所以SQL的事务是支持的。FileTable也可以通过正常的Transact-SQL命令查询和更新。他们还集成了SQL Server管理工具和功能，如备份和恢复。
*/

 

-- 创建数据库时创建 FILESTREAM 文件组

CREATE DATABASE FileStreamDB
ON
PRIMARY
(
NAME = FileStreamDB,

FILENAME = 'c:\databasefs\FileStreamDB.mdf',
SIZE = 50MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
),

FILEGROUP FileStreamGroup CONTAINS FILESTREAM(
NAME = FileStreamFile,
FILENAME = 'c:\databasefs\FileStreamFile',
MAXSIZE = 50 MB
)

LOG ON (
NAME = FileStreamDB_log,
FILENAME = 'c:\databasefs\FileStreamDB_log.ldf'
)
WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileStreamPath' )

GO

 

-- 在数据库级别上指定 非事务性访问级别 和 FileTable目录(数据库须独占)
ALTER DATABASE [FileStreamDB]
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileStreamPath' )

-- 启用后即可查看到其状态信息
SELECT DB_NAME(database_id) [database],non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options
WHERE non_transacted_access_desc <> 'OFF'

SELECT DB_NAME(database_id) [database],directory_name
FROM sys.database_filestream_options
WHERE directory_name IS NOT NULL

 

USE [FileStreamDB]

GO

CREATE TABLE [DocumentStore]
AS FileTable
WITH (

--文件目录,不区分大小写,不指定则为filetable名称[DocumentStore]

FileTable_Directory = N'FileStreamPath',

FileTable_Collate_Filename = database_default

)
GO
-- 查看 FileTable

SELECT * FROM [dbo].[DocumentStore]

-- 获取特定 FileTable 或当前数据库的根级 UNC 路径。

SELECT FileTableRootPath();

SELECT FileTableRootPath(N'DocumentStore');

SELECT FileTableRootPath(N'dbo.DocumentStore');

 

-- 也可以用GetFileNamespacePath查看 FileTable 中文件或目录的 UNC 路径。

SELECT
file_stream.GetFileNamespacePath()
,file_stream.GetFileNamespacePath(1, 0)
FROM [dbo].[DocumentStore]

-- 删除表 FileTable 中的记录，文件也会被删除

DELETE FROM [dbo].[DocumentStore] WHERE stream_id = 'BA483ECA-AE0E-E511-8367-005056C00008'

 

SELECT * FROM [dbo].[DocumentStore]