您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server FILESTREAM 深度解析：大型二進位資料儲存的最佳實踐

編輯：關於SqlServer

FILESTREAM 是 SQL Server 2008 引入的一項創新功能，專為儲存大型二進位物件（BLOB）而設計。它突破了傳統將所有資料集中存放於資料庫檔案的限制，允許將 BLOB 資料以獨立檔案形式儲存在 NTFS 檔案系統中，同時保持與資料庫引擎的緊密整合。這項技術巧妙結合了「資料庫僅存路徑」與「資料庫內嵌 varbinary(max)」兩種傳統方案的優點，並有效規避了它們各自的缺陷。

傳統檔案管理的兩難困境

在 FILESTREAM 出現之前，處理業務系統檔案通常有兩種做法：

檔案系統儲存：資料庫僅記錄檔案路徑，實體檔案置於檔案伺服器（如 NFS）。應用程式需額外連線至檔案伺服器讀取。此法雖減輕資料庫負擔，但受磁碟 I/O 影響大，效能不穩定，且難以實現一致性備份。
資料庫內嵌儲存：直接以 varbinary(max) 或 image 型別存入資料表。雖解決備份問題，但龐大欄位會嚴重擴大資料庫體積，衝擊緩衝池效能，查詢速度顯著下降。

FILESTREAM 的核心優勢

FILESTREAM 將 SQL Server 資料庫引擎與 NTFS 檔案系統融為一體。透過 Transact-SQL 即可對 FILESTREAM 資料進行插入、更新、查詢、搜尋與備份。關鍵在於它利用 NT 系統快取來緩存檔案資料，完全繞過 SQL Server 緩衝池，避免大型物件擠佔寶貴的記憶體資源，從而保障查詢處理的高效能。

一、FILESTREAM 環境配置步驟

1. 啟用執行個體層級的 FILESTREAM

開啟 SQL Server 組態管理員（開始 → 所有程式 → Microsoft SQL Server 2008 R2 → 組態工具），右鍵對應的 SQL Server 服務選擇「屬性」，切換至 FILESTREAM 索引標籤，勾選下列選項：

啟用 FILESTREAM 供 Transact-SQL 存取
啟用 FILESTREAM 供檔案 I/O 存取
允許遠端用戶端存取 FILESTREAM 資料

2. 設定伺服器層級存取等級

在 SSMS 中執行以下指令碼，將存取等級設為 2（完整啟用）：

EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

完成後必須重新啟動 SQL Server 服務才能生效。

二、實戰範例：建立與使用 FILESTREAM 資料表

1. 新增 FILESTREAM 檔案群組與檔案

-- 建立包含 FILESTREAM 的檔案群組
ALTER DATABASE [Archive] ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM;
GO

-- 將實體目錄掛載至檔案群組
ALTER DATABASE [Archive] ADD FILE (
    NAME = N'FileStream',
    FILENAME = N'D:\Company\Data\SQL Server\FileStream'
) TO FILEGROUP [FileStreamGroup];
GO

2. 建立包含 FILESTREAM 欄位的資料表

注意：擁有 FILESTREAM 欄位的資料表，每一列必須具備唯一的 ROWGUIDCOL 識別碼。

CREATE TABLE Archive.dbo.Attachment (
    [ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL PRIMARY KEY,
    [FileName] NVARCHAR(100) NULL,
    [CreateUser] NVARCHAR(100) NULL,
    [CreateDatetime] DATETIME NULL,
    [Content] VARBINARY(MAX) FILESTREAM NULL
) FILESTREAM_ON [FileStreamGroup];

3. 插入測試資料

透過 T-SQL 直接插入：

INSERT INTO Attachment VALUES
    (NEWID(),'File Name 1','shg.cpan', GETDATE(),NULL),
    (NEWID(),'File Name 1','shg.cpan', GETDATE(),CAST('' AS VARBINARY(MAX))),
    (NEWID(),'File Name 1','shg.cpan', GETDATE(),CAST('This is a attachment, which contains all introduction for filestream' AS VARBINARY(MAX)));

透過應用程式（C# 範例）串流上傳大型檔案：

using (SqlConnection conn = new SqlConnection("server=10.7.15.172;database=Archive;uid=sa;pwd=1234;Connect Timeout=180")) {
    conn.Open();
    using (SqlCommand cmd = conn.CreateCommand()) {
        string id = Guid.NewGuid().ToString();
        cmd.CommandText = "INSERT INTO Attachment VALUES('" + id + "','File Name 2','shg.cpan','" + DateTime.Now + "',@content)";
        SqlParameter param = new SqlParameter("@content", SqlDbType.VarBinary, 1000000000);
        param.Value = File.ReadAllBytes(@"D:\Folder\131 u_ex151207.log");
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
    conn.Close();
}

4. 查詢與驗證

檢視檔案大小（MB）與內容：

SELECT DATALENGTH(CONTENT)/(1024.0 * 1024.0) AS MB, * FROM ATTACHMENT;

在檔案系統中，FILESTREAM 目錄下會自動生成對應的實體檔案（無副檔名）。手動加上正確副檔名（如 .xls、.log）即可直接用對應軟體開啟，驗證資料完整性。

三、關鍵注意事項與最佳實踐

適用門檻：平均檔案大小 超過 1 MB 時建議使用 FILESTREAM；較小物件仍以 varbinary(max) 內嵌儲存效能更佳。
容錯移轉叢集支援：可部署於容錯移轉叢集，但 FILESTREAM 檔案群組必須置於共享磁碟資源上。
功能相容性：部分 SQL Server 功能（如資料庫鏡像、複寫等）對 FILESTREAM 有限制，請參考官方文件：FILESTREAM 與其他 SQL Server 功能的相容性。

總結而言，FILESTREAM 為大型二進位資料提供了「資料庫管理便利性」與「檔案系統高效能」的完美平衡，是現代化資料架構中不可或缺的利器。