SQL Server的FileStream和FileTable深入剖析

互連網時代資料是爆炸式增長，我們常常需要把結構化資料和非結構化資料（如文檔，簡報，視頻，音頻，映像）儲存在一起。通常有幾種方案：

1。在資料庫中儲存結構化資料，在檔案系統中儲存非結構化資料，然後資料庫裡有一個欄位記錄檔案系統的路徑，雖然這種方法成本合算，但它引入了額外的複雜度，因為你需要手動去保證跨關係和非關係系統管理事務的完整性。
2。將結構化資料和非結構化資料都儲存在資料庫中，多年以來，資料庫一直都支援儲存非關係資料，如二進位大對象，或BLOB，SQL Server稱之為varbinary資料類型，這樣的好處是能充分利用資料庫的特性（如事務支援，備份和恢複支援，整合式安全性的支援，全文檢索搜尋支援等），但成本費用會更高，所需的磁碟空間更多，因為是儲存在同一行資料裡，儲存和檢索時間更長，對應用程式的整體效能也會有負面影響。（如果你寫查詢時用select * from xxx 就很慢了）
3。SQL Server 2008中引入的FILESTREAM資料類型來儲存非結構化資料，如文檔，簡報，視頻，音頻，映像，資料庫中儲存的是檔案系統上的一個指標。在SQL Server 2008中，新的FILESTREAM（檔案流）特性是在現有的varbinary(max)資料類型之上實現的，你可以在伺服器的檔案系統上儲存真實的資料，但可以在資料庫上下文內管理和訪問。自動保證了事務的完整性。
4。SQL Server 2012的FileTable則進一步增強，它可以讓應用程式通過引入FileTable整合其儲存和資料管理組件，允許非事務性訪問，提供整合的對非結構化資料和中繼資料的全文檢索搜尋和語義搜尋。

下面詳細談談這兩項新功能。
瞭解在SQL Server 2008中的FileStream
FILESTREAM資料類型作為varbinary（max）列實現的，資料是儲存在NTFS檔案系統，資料庫中存放的是指標。在這種情況下，儲存不再是BLOB的2GB大小的限制，只是受制於NTFS檔案系統的檔案大小。FileStream是預設禁止的，所以你需要對varbinary（max）列指定FILESTREAM屬性。這樣SQL Server才不會把BLOB存到SQL Server資料庫，而是存到NTFS檔案系統。
將BLOB資料存放區在NTFS檔案系統上的帶來了一些好處：
和直接操作NTFS檔案系統的資料流的效能一樣
FILESTREAM資料沒有使用SQL Server緩衝池的，因此SQL Server緩衝池的查詢處理，並不會受到FILESTREAM資料的影響。
不再有BLOB的2G大小的限制。
事務的一致性。
SQLServer整合的安全模型。
備份和恢複時，會包含FILESTREAM BLOB資料。
支援全文檢索搜尋。

用SELECT，INSERT，UPDATE和DELETE語句，操作帶FILESTREAM資料的表，效能沒有變慢。
為了使用這項新功能，我們首先需要在執行個體層級啟用它（在安裝過程中，或通過修改SQL Server執行個體的屬性，或通過使用sp_configure來更改執行個體屬性），然後建立或修改資料庫，有一個檔案組有FileStream屬性，然後建立一個錶帶有varbinary（max）資料類型列，並指定FileStream屬性。

瞭解SQL Server 2012的FileTable
FileTable使用FILESTREAM的基礎上進一步加強，它既允許直接的，事務性的，存取FILESTREAM列大型資料。FileTable表也可以配置為允許非事務性訪問檔案，而無需事先SQLServer授權。

FileTable是一種特殊類型的表，它的結構是固定的，不像普通使用者表可以定義自己的欄位，就好像它是一個檔案夾中的檔案系統。一個的FileTable包含的FileStream隨著幾個檔案級屬性（file_id，名稱，路徑，建立日期，修改日期，最後訪問時間等），檔案和目錄階層的資料。這意味著FileTable中的每一行資料代表一個檔案系統上的檔案或目錄。

FileTable在檔案系統表現為一個indows共用目錄，裡面有檔案和目錄資料，你可以通過非事務性的檔案訪問（基於Windows API的應用程式能夠訪問檔案，而無需SQLServer存取權限）。對於Windows應用程式，這看起來像一個正常的網際網路共用位置的檔案和目錄。應用程式可以使用Windows API來管理這個網際網路共用位置的檔案和目錄。

Windows API操作是非事務性的，和資料庫是不相關。然而，FileTable是基於FileStream實現的，所以SQL的事務是支援的。FileTable也可以通過正常的Transact-SQL命令查詢和更新。他們還整合了SQL Server管理工具和功能，如備份和恢複。

我們需要分開配置FileTable和FILESTREAM。這意味著，我們可以繼續只使用FileStream的功能，而無需啟用非事務性訪問或建立FileTable。
開始使用SQL Server 2012的FileTable
啟用FileStream 複製代碼 代碼如下: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

按 Ctrl+C 複製代碼建立LearnFileTable資料庫 複製代碼 代碼如下: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

按 Ctrl+C 複製代碼檢查FileStream/FileTable 複製代碼 代碼如下:-- 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'

建立FileTable 複製代碼 代碼如下:USE LearnFileTable
GO
CREATE TABLE MyFirstFileTable AS FileTable
WITH
(
FileTable_Directory = 'MyFirstFileTable',
FileTable_Collate_Filename = database_default
);
GO

按 Ctrl+C 複製代碼如果我們建立FileTable前沒有啟用FileStream，會報錯
Msg 1969, Level 16, State 1, Line 1 Default FILESTREAM filegroup is not available in database '<database_name>'建立後，我們查詢一下，沒有記錄
USE LearnFileTable SELECT * FROM [dbo].[MyFirstFileTable]

在企業管理器選擇FileTable，右鍵 "Explorer FileTable Directory" link as shown below:

你會看到網際網路共用目錄. 手動添加幾個檔案到該目錄，我們返回SQLServer企業管理器看看有什麼事情發生:

再運行一次查詢語句
USE LearnFileTable SELECT * FROM [dbo].[MyFirstFileTable]

總結
在這篇文章中，我談到了利用SQL Server的FileStream和FileTable功能儲存非結構化的資料。 FILESTREAM功能在檔案系統中儲存非結構化資料，並把檔案的指標儲存在資料庫，而FileTable進一步擴充了這一功能允許非事務性訪問（訪問檔案，而無需事先授權，共用位置）。換句話說，有了這個功能，我們可以通過檔案系統來管理非結構化資料，而不是在SQL Server管理，卻依然可以在SQL Server中的事務訪問這些檔案。