SQL Server 大量寫入（bcp / Bulk Copy）與 TDS 協定

目錄

• 為什麼大量寫入時 bcp 特別快
• 三條大量寫入路徑
• 為什麼 bulk copy 快（核心）
• TDS 是什麼
• TDS 的 Bulk Load Request
• bcp 實戰
• 程式內 Bulk Copy 實戰
• 影響速度的關鍵條件：最小記錄
• bulk load 專屬陷阱
• 載入後讓資料上線：SWITCH 與 MERGE
• 怎麼量測與疑難排解
• 其他資料庫的對應
• 常見問題
• 參考資源與總結

為什麼大量寫入時 bcp 特別快

把大量資料寫進 SQL Server 時，逐筆 INSERT 的速度會隨資料量線性惡化；改用 bcp 或 bulk copy 介面，往往能快上一兩個數量級。差距不是來自「指令比較好」，而是來自一條完全不同的寫入路徑：bulk load 在底層走的是 TDS（Tabular Data Stream）協定中專用的批次資料串流，繞過了逐筆 INSERT 在網路、解析、交易記錄上的固定開銷。

理解這件事需要三層拼起來：

工具層     bcp / SqlBulkCopy / BULK INSERT
   ↓
協定層     TDS Bulk Load Request（COLMETADATA + ROW token 串流）
   ↓
儲存層     最小記錄（minimal logging）+ 批次配置 extent

這篇由上而下把這三層講清楚。

三條大量寫入路徑

把「大量寫入」拆開，其實有三條不同的路徑，前兩條在線路上送的是同一個 TDS Bulk Load Request：

關鍵區別

• bcp 工具 vs 程式內 API：兩者最終都在 TDS 上送出 Bulk Load Request，速度上限相同。差別在前置成本——bcp 需要先把資料落地成檔案、再啟動一個外部程序；程式內 API 直接從記憶體串流，省掉檔案那一步。資料已經在程式裡時，用 SqlBulkCopy 這類 API 可避免「寫檔 → 讀檔」的多餘 I/O；bcp 的優勢則是免寫程式、跨語言、適合臨時或排程匯入。

• 客戶端 bulk load vs BULK INSERT：前兩條是「客戶端把資料串流給伺服器」；BULK INSERT 是「伺服器自己去讀一個檔案」。後者的檔案必須放在 SQL Server 程序看得到的位置（本機磁碟或它有權限的共享），資料不經由客戶端的 TDS 連線傳輸。

為什麼 bulk copy 快（核心）

把逐筆 INSERT 的成本拆開，可以看到 bulk load 是逐項把它們消掉的。

逐筆 INSERT 的四大成本

對每一筆 INSERT INTO t VALUES (...)：

1. 網路往返（round-trip）：每筆是一次 TDS 請求/回應。1,000,000 筆 = 1,000,000 次往返，延遲累加。
2. 解析與編譯（parse + compile）：每個語句字串要在伺服器端剖析、產生執行計畫（未參數化時尤其貴）。
3. 交易記錄（transaction log）：每筆變更逐列寫入 log，且預設每筆是一個自動提交交易，log flush 頻繁。
4. 逐筆協定開銷：每筆都帶 token、欄位描述等固定 overhead。

bulk load 怎麼繞過

所以「100 萬筆逐筆 INSERT」和「100 萬筆 bulk load」的差距，本質是 O(N) 次往返 + 編譯 + 逐列 log 變成 接近 O(1) 次往返 + 一次設定 + 批次配置 log。資料量越大，差距越明顯。

TDS 是什麼

TDS（Tabular Data Stream） 是 SQL Server（與早期 Sybase）用來在客戶端與資料庫伺服器之間溝通的應用層協定。所有與 SQL Server 的互動——登入、送 SQL、收結果、bulk load——都封裝在 TDS 之上。微軟以開放規格 [MS-TDS] 公開了它。

定位

應用程式 / driver（ODBC、JDBC、ADO.NET…）
        ↓  產生 TDS 訊息
      TDS（應用層）
        ↓  封裝在
   TCP（預設埠 1433；也支援具名管道 / 共享記憶體）

ODBC、JDBC、ADO.NET 這些 driver 並不是「另一種協定」，它們是把你的 API 呼叫翻譯成 TDS 訊息的程式庫。（ODBC 架構詳見 ODBC 完全指南。）

封包與訊息類型

TDS 把資料切成封包（packet），每個封包有 8 位元組標頭（類型、狀態、長度、SPID 等）加上資料。常見的訊息類型：

Token 串流

TDS 的回應（以及 bulk load 的請求）不是純文字，而是一連串 token。例如查詢結果是 COLMETADATA（欄位描述）後面接多個 ROW，最後 DONE。bulk load 正是反過來用同一套 token 把資料送進伺服器。

加密與認證

加密在 Pre-Login 階段協商：客戶端與伺服器交換加密選項，必要時進行 TLS 交握，之後的 TDS 流量在 TLS 之上。連線字串的 Encrypt / TrustServerCertificate 控制這個行為。注意：開啟加密會增加 CPU 與一些延遲，大量 bulk load 的吞吐可能因此下降，是調效時要納入的變因。

TDS 的 Bulk Load Request

這是「為什麼快」在協定層的答案，也是 bcp 與程式內 API 共同走的路徑。

流程

1. 客戶端送一個 SQL Batch：INSERT BULK <table> (<欄位定義>)
        ↓
2. 伺服器準備接收，回應
        ↓
3. 客戶端送 Bulk Load 資料串流：
        COLMETADATA   ← 描述接下來資料的欄位（型別、長度…），只送一次
        ROW           ← 第 1 列（緊湊二進位）
        ROW           ← 第 2 列
        ...           ← 連續多列，不再逐筆送 SQL
        DONE          ← 結束

重點

• 資料串流的格式，和從伺服器 SELECT 出資料時一樣——只是方向相反。COLMETADATA + ROW... + DONE 這套 token 同時用於「讀出」與「灌入」。
• 因為送的是二進位 token 而非 SQL 文字，bulk load 的實際資料值不會出現在一般的查詢追蹤（trace）裡——追蹤只看得到 INSERT BULK 那個設定語句，看不到每一列的值。除錯時要注意這點。
• 資料是「依 COLMETADATA 描述的傳輸格式」而非儲存頁面格式，由伺服器收下後寫入頁面。

名詞釐清：嚴格說沒有官方叫「TDS API」的東西。TDS 是線路協定；你在程式裡呼叫的是各 driver 的 bulk copy API（如 SqlBulkCopy），由它產生上面的 TDS Bulk Load Request。

bcp 實戰

bcp（Bulk Copy Program）是命令列工具。在 Windows 上是 bcp.exe；在 macOS / Linux 上由 mssql-tools（或新版 go-sqlcmd）提供，指令名是 bcp（無 .exe）。

基本語法

bcp {資料表 | 查詢} {in | out | queryout | format} 資料檔 [選項]

匯入範例

# 從 data.csv 匯入 SalesDB 的 dbo.Orders（SQL 驗證、字元格式、批次 1 萬筆、表鎖）
bcp SalesDB.dbo.Orders in data.txt \
  -S myserver.database.windows.net -U sa -P '***' \
  -c -t ',' -b 10000 -h "TABLOCK"

常用選項

格式檔（format file）

當資料檔欄位順序、分隔符與資料表不完全對應時，用格式檔描述對應關係：

# 產生一份非 XML 格式檔（互動式按 Enter 用預設）
bcp SalesDB.dbo.Orders format nul -f orders.fmt -c -S myserver -U sa -P '***'

# 之後用格式檔匯入
bcp SalesDB.dbo.Orders in data.txt -f orders.fmt -S myserver -U sa -P '***'

macOS 安裝

# Homebrew（Microsoft 套件庫）
brew install microsoft/mssql-release/mssql-tools18
# 安裝後指令是 bcp / sqlcmd（注意 18 版預設要求加密，連舊伺服器可能要 -C 之類選項）

程式內 Bulk Copy 實戰

資料已經在程式記憶體裡時，直接用 bulk copy API 省掉檔案落地。各語言的 driver 各有實作。

.NET：SqlBulkCopy

using Microsoft.Data.SqlClient;

using var bulk = new SqlBulkCopy(connStr, SqlBulkCopyOptions.TableLock)
{
    DestinationTableName = "dbo.Orders",
    BatchSize = 10000,       // 對應 bcp 的 -b
    BulkCopyTimeout = 600,   // 秒
};
bulk.ColumnMappings.Add("OrderId", "OrderId");
bulk.ColumnMappings.Add("Amount", "Amount");

// 來源可以是 DataTable、DataRow[]，或最省記憶體的 IDataReader（串流）
bulk.WriteToServer(reader);

SqlBulkCopyOptions 旗標對應前面那些載入行為：TableLock、KeepIdentity、FireTriggers、CheckConstraints、KeepNulls。

JDBC（Microsoft JDBC Driver）

兩種用法：

# 1. 連線屬性：讓批次 INSERT 自動改走 bulk copy（driver 8.4+）
jdbc:sqlserver://host;databaseName=SalesDB;useBulkCopyForBatchInsert=true

// 2. 直接用 SQLServerBulkCopy 類別
try (SQLServerBulkCopy bulk = new SQLServerBulkCopy(connection)) {
    bulk.setDestinationTableName("dbo.Orders");
    SQLServerBulkCopyOptions opts = new SQLServerBulkCopyOptions();
    opts.setBulkCopyTimeout(600);
    opts.setBatchSize(10000);
    opts.setTableLock(true);
    bulk.setBulkCopyOptions(opts);
    bulk.writeToServer(sourceResultSet);   // 來源是 ResultSet 或自訂 ISQLServerBulkData
}

ODBC：bulk copy 函式

底層 C API，也是 bcp 工具內部所用：bcp_init → bcp_bind（綁定欄位）→ bcp_sendrow（逐列送，但走 bulk 串流）→ bcp_batch（提交一批）→ bcp_done。

何時免去 bcp 直接用 API

• 資料已在程式記憶體 / 來自另一個查詢 → 用 API 串流，免寫檔。
• 需要與既有交易、連線池整合 → 用 API。
• 臨時匯入、排程腳本、跨語言、不想寫程式 → 用 bcp 工具。

影響速度的關鍵條件：最小記錄

bulk load「快」的儲存層關鍵是最小記錄（minimal logging）：交易記錄只記錄「配置了哪些 extent」，而不是每一列的內容，大幅降低 log I/O。但它不會自動發生，要同時滿足條件。

前提條件

1. 復原模式為 SIMPLE 或 BULK_LOGGED（FULL 模式下無法最小記錄）。 （復原模式與 log 的關係見 交易與 ACID 完全指南。）
2. 取得表級鎖：加 TABLOCK 提示，或用 sp_tableoption 設定 table lock on bulk load。
3. 目標資料表的索引狀態符合下表。

依目標結構的記錄行為

版本差異（fast load context）

讓叢集索引也能最小記錄的「fast load context」：

• SQL Server 2008～2014：需開啟 trace flag 610。
• SQL Server 2016 起：預設開啟，可用 trace flag 692 關閉。

實務做法

最快的大量載入常見組合：空的堆積表 + TABLOCK + SIMPLE/BULK_LOGGED，載入完成後再建索引。原因見下一節與「載入後上線」。

bulk load 專屬陷阱

bulk load 為了快，預設跳過了好幾項一般 INSERT 會做的事。不知道的話會踩雷：

1. 觸發器預設不觸發

bulk load 預設不執行 INSERT 觸發器。需要的話加 FIRE_TRIGGERS（bcp -h "FIRE_TRIGGERS" / SqlBulkCopyOptions.FireTriggers），但會犧牲速度。

2. CHECK / 外鍵約束預設不檢查

預設略過 CHECK 與 FOREIGN KEY 驗證，載入後約束會被標記為「不受信任（not trusted）」，可能影響查詢最佳化。要驗證就加 CHECK_CONSTRAINTS，或事後執行：

ALTER TABLE dbo.Orders WITH CHECK CHECK CONSTRAINT ALL;

（外鍵與參照完整性見 外鍵與參照完整性完全指南。）

3. IDENTITY 欄位

預設由伺服器重新編號 IDENTITY。要保留來源值用 KEEPIDENTITY（bcp -E）。

4. 批次大小 = 交易與回滾粒度

-b / BatchSize 切出來的每一批是一個交易。中途失敗時，已提交的批次不會回滾，目標表會處於「載入了一部分」的狀態。需要全有全無時，要嘛整個包在一個外層交易，要嘛載入到暫存表確認無誤後再上線（見下一節）。

5. 預設值與 NULL

是否套用欄位的 DEFAULT、空白要當 NULL 還是空字串，由 KEEPNULLS 等選項決定，跨工具行為略有差異，匯入前確認對應。

載入後讓資料上線：SWITCH 與 MERGE

bulk load 是「只進、灌空表最快」的操作；真實管線通常先載入暫存表，再讓資料上線。依需求有兩種模式。

模式 A：staging + 分割切換（SWITCH）

適合大量附加到一張大型分割表、且要即時上線的情境（資料倉儲、時序資料）。核心：ALTER TABLE ... SWITCH 是 metadata-only 操作——不搬資料，只翻轉中繼資料指標，不論幾百萬列都近乎瞬間完成。

1. 建一張結構完全相同的 staging 表（同檔案群組、對齊索引）
2. 用 bcp / bulk copy 離線灌進 staging（盡情用 TABLOCK + 最小記錄、載入後再建索引；
   過程不碰正式表、不擋線上讀取）
3. ALTER TABLE staging SWITCH TO target PARTITION n;   ← 瞬間上線

對比直接灌正式表：昂貴的部分（寫資料 + 建索引）全在 staging 表離線完成，正式表只承受一次毫秒級的指標翻轉，不會因大量寫入而長時間鎖表或撐爆 log。

SWITCH 要成立有一組嚴格條件（同檔案群組、結構與索引對齊、staging 表要有界定範圍的 CHECK 約束、目標分割須為空等）。分割表的設計、索引對齊、SWITCH 完整條件與滑動視窗維護，見 SQL Server 資料表分割。

模式 B：staging + MERGE / UPSERT

需要更新或合併既有資料（去重、idempotent 同步）時：bulk load 進暫存表，再用一次集合式 MERGE 寫進正式表。

-- 概念示意：把 staging 合併進 target
MERGE dbo.Orders AS t
USING dbo.Orders_Staging AS s ON t.OrderId = s.OrderId
WHEN MATCHED THEN UPDATE SET t.Amount = s.Amount
WHEN NOT MATCHED THEN INSERT (OrderId, Amount) VALUES (s.OrderId, s.Amount);

這等於用「快但笨」的 bulk load + 「聰明但較慢」的 set-based MERGE 各取所長。MERGE / ON CONFLICT 等各 DB 的 upsert 語法與陷阱見 UPSERT 與衝突處理完全指南。

怎麼量測與疑難排解

公平地量測快多少

避免靠印象，用可重現的方式比較：

• 吞吐量：以 rows/sec 或 MB/sec 為單位，固定資料集分別跑逐筆 INSERT 與 bulk load。
• 計時：SET STATISTICS TIME ON;，或記錄起訖時間。
• 找批次甜蜜點：掃幾個 -b / BatchSize（如 1k、1 萬、10 萬），太小往返多、太大記憶體與回滾成本高。
• 控制變因：注意暖機（第一次有快取效應）、目標表是否為空、是否觸發最小記錄、是否開了加密。

常見問題排解

其他資料庫的對應

bulk load 不是 SQL Server 獨有，主流 DB 都有「繞過逐筆 INSERT」的大量載入機制：

共通原理一致：一次設定欄位 + 連續串流資料 + 盡量減少逐列記錄。換到別的 DB 時，找它的「bulk / copy / load」機制即可。

常見問題

問題 1：bcp 和 SqlBulkCopy 哪個快？

速度上限相同，因為兩者在 TDS 上送同一個 Bulk Load Request。差別在前置成本：bcp 要先把資料寫成檔案、再開外部程序；SqlBulkCopy 直接從記憶體串流。資料已在程式裡就用後者，臨時/跨語言匯入就用前者。

問題 2：BULK INSERT 和 bcp in 差在哪？

BULK INSERT 是 T-SQL，由伺服器讀檔，檔案要在伺服器看得到的位置；bcp in 由客戶端讀檔並透過 TDS 串流給伺服器。前者省掉客戶端傳輸但需要伺服器端檔案存取權，後者適合檔案在客戶端的情境。

問題 3：為什麼我用了 bulk load 還是很慢、log 還是爆？

最常見原因是沒觸發最小記錄：復原模式是 FULL、沒加 TABLOCK、或目標是「非空的叢集索引表」。把目標改成空堆積表 + TABLOCK、復原模式設 SIMPLE/BULK_LOGGED，載入後再建索引。

問題 4：為什麼 trace 看不到 bulk load 的資料值？

因為 bulk load 送的是 TDS 的二進位 token 串流，不是 SQL 文字。追蹤只記得到 INSERT BULK 設定語句，看不到逐列的值。要看封包層得用網路擷取工具（Wireshark 內建 TDS 解析器）。

參考資源與總結

參考資源

核心要點

快的本質 = 繞過 SQL 解析層 + 最小記錄

工具       bcp / SqlBulkCopy / BULK INSERT
  ↓        前兩者走同一個…
協定       TDS Bulk Load Request（INSERT BULK → COLMETADATA → ROW… → DONE）
  ↓        符合條件時觸發…
儲存       最小記錄（log 只記 extent 配置，不記每列）

方法 × 速度 × 適用情境

最小記錄速查

建立日期：2026-06-30