SQL Server 2005 檔案與檔案群完整指南：架構、效能優化與實戰操作

一、 檔案與檔案群的核心概念與關係

在 SQL Server 2005 中，每個資料庫至少包含一個主資料檔案（建議副檔名 .mdf）與一個交易記錄檔（建議副檔名 .ldf）。資料檔案是資料庫的實體儲存體現，而檔案群則是一種邏輯容器，允許將分散於多個邏輯磁碟分割區的次要資料檔（建議副檔名 .ndf）歸組管理。透過檔案群，我們能實現 I/O 負載平衡、彈性擴充儲存空間，並針對特定資料表或索引指定儲存位置。

例如，可在三顆實體硬碟上分別建立 Data1.ndf、Data2.ndf、Data3.ndf，並將它們歸入檔案群 fgroup1。當在該檔案群上建立資料表時，查詢會自動分散至三顆磁碟並行讀取，大幅提升效能。雖然 RAID 條帶化也能達到類似效果，但檔案群優勢在於：新增磁碟時只需加入新檔案至群組，無需重建陣列；且當資料庫大小超過單一 Windows 檔案上限時，次要檔案可讓資料庫持續成長。

二、 單一 MDF 檔案的常見隱憂

1. 檔案過大導致觸及系統上限

預設僅有 .mdf 與 .ldf 兩個檔案時，所有使用者資料集中於主資料檔。隨著業務增長，.mdf 會迅速膨脹，極易觸及 Windows 單一檔案大小限制（NTFS 理論上限 16 TB，但實務運作建議控制在較小範圍），導致資料庫無法寫入甚至崩潰。

2. 無法發揮磁碟陣列並行優勢

企業級伺服器常配置磁碟陣列（RAID），本質是多顆磁碟並行運作以提升吞吐量。若資料庫僅有一個 .mdf，I/O 請求只能落在單一邏輯磁碟上，將原本的並行架構退化為序列存取，白白浪費昂貴的硬體投資。將資料拆分為多個 .ndf 分散於不同磁碟，再歸入同一檔案群，即可讓查詢並行讀寫，充分榨乾磁碟陣列效能。

三、 查詢檔案與檔案群資訊的 T-SQL

SELECT 
    df.[name] AS logical_name,
    df.physical_name,
    df.[size],
    df.growth,
    f.[name] AS filegroup,
    f.is_default
FROM sys.database_files df
JOIN sys.filegroups f ON df.data_space_id = f.data_space_id;

四、 MSDN 官方架構說明重點整理

• 主要資料檔：啟動資訊、指向其他檔案，建議 .mdf。
• 次要資料檔：可選、使用者自訂，跨磁碟分散資料、突破單檔大小限制，建議 .ndf。
• 交易記錄檔：至少一個，儲存復原資訊，建議 .ldf。
• 檔案群分類：
  • 主要檔案群：含主檔案與未指定群組的次要檔，系統表皆在這裡。
  • 使用者自訂檔案群：依需求建立，利於管理與資料配置。
  • 預設檔案群：未指定群組時的落腳處，預設為 PRIMARY，可用 ALTER DATABASE 變更。

五、 完整實戰操作範例

5.1 建立資料庫與多檔案群

USE master;
GO
CREATE DATABASE db_Study
ON PRIMARY 
(NAME = 'db_study_Primary', FILENAME = 'D:\study\db_study_pri.mdf'),
FILEGROUP db_Study_filegroup1
(NAME = 'db_study_fg_data1', FILENAME = 'D:\study\db_study_fg_data1_1.ndf'),
(NAME = 'db_study_fg_data2', FILENAME = 'D:\study\db_study_fg_data2_2.ndf'),
FILEGROUP db_Study_filegroup2
(NAME = 'db_study_fg_data3', FILENAME = 'D:\study\db_study_fg_data3_1.ndf')
LOG ON
(NAME = 'db_study_log', FILENAME = 'D:\study\db_study.ldf');
GO

5.2 變更預設檔案群

ALTER DATABASE db_Study MODIFY FILEGROUP db_Study_filegroup1 DEFAULT;

5.3 指定檔案群建立資料表與索引

-- 資料表建立於預設群組 (db_Study_filegroup1)，大型物件 (image) 存放於 db_Study_filegroup2
CREATE TABLE my_test (
    ID INT PRIMARY KEY,
    [Name] VARCHAR(10),
    PIC IMAGE
) TEXTIMAGE_ON db_Study_filegroup2;

-- 索引專屬建立於 db_Study_filegroup2
CREATE INDEX ix_my_test ON my_test(ID) ON db_Study_filegroup2;
GO

5.4 安全移除資料檔

-- 1. 將資料遷移至同群組其他檔案
DBCC SHRINKFILE(db_study_fg_data1, EMPTYFILE);
GO
-- 2. 移除檔案
ALTER DATABASE db_Study REMOVE FILE db_study_fg_data1;
GO

掌握檔案與檔案群設計，是 SQL Server 2005 實現高效能、高擴充性資料庫架構的關鍵基石。