SQL Server 資料庫開發十大頂級實戰技巧

1. 嚴格統一資料型別

應用程式各層 (資料庫欄位、預存程序參數、ADO.NET `SqlParameter`、實體類別屬性) **必須型別與長度完全一致**。型別不匹配導致隱含轉換，索引失效 (Index Scan 取代 Seek)，嚴重拖慢效能。

2. 大量更新必須分批

單一大交易鎖表、塞爆記錄檔、導致應用逾時。策略：
• 批次 10,000~50,000 筆 (視 I/O 與鎖爭用調整)
• 搭配頻繁記錄備份控制記錄檔增長
• 參考閾值：ADO.NET 預設指令逾時 30 秒，更新預估超過 20~25 秒即分批

WHILE (1=1)
BEGIN
    UPDATE TOP (10000) Person SET Status = 2 WHERE Status = 1;
    IF @@ROWCOUNT = 0 BREAK;
END

3. 善用 `sp_msforeachtable` / `sp_msforeachdb` 批量作業

對所有資料表/資料庫執行同一操作，避免手寫游標。
範例：除 `tempdb` 外全庫完整備份、停用/啟用所有外鍵、清空所有表資料。

EXEC sp_msforeachdb 'IF ''?'' <> ''tempdb'' BACKUP DATABASE ? TO DISK=''c:\backups\?.bak'' WITH INIT';

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSForEachTable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? ELSE TRUNCATE TABLE ?';
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

4. 建立資料庫版本控管

建立 `Version` 資料表 (版本號、部署時間、腳本編號)。部署腳本先比對版本號，低於或等於目前版本則跳過。參考 `AdventureWorks.AWBuildVersion` 設計。

5. 極小化網路往返

單一預存程序可回傳**多結果集**，以 `DataSet`/`DataTable` 集合一次取回，避免 N+1 查詢問題。網頁若呼叫 15 次 DB，延遲疊加極大。

6. 避免 `SELECT *`

實測：少撈一個大欄位 (如 `NVARCHAR(MAX)`) 速度提升 3 倍以上。遵循「需多少，選多少」。

7. `COUNT(*)` 不慢，`COUNT(大欄位)` 才慢

實測：`COUNT(*)` ≈ `COUNT(PK)` < `COUNT(短欄位)` << `COUNT(長欄位)`。優化器會自動選最小欄位統計。

8. `ORDER BY` 叢集索引欄位最快

不排序 ≈ 叢集索引排序 << 非叢集索引排序。正序/倒序效能相近。

9. 巢狀 `TOP` 限制物理 I/O

SELECT TOP 10 * FROM (
    SELECT TOP 10000 gid, fariqi, title FROM tgongwen WHERE neibuyonghu='辦公室' ORDER BY gid DESC
) AS a ORDER BY gid ASC;

子查詢雖掃 1 萬筆，外層僅回 10 筆，**物理 I/O 決定回應時間**，`TOP` 經優化器深度優化。

10. 理解查詢優化器與 SARG

優化器會自動重排 `WHERE` 條件順序，**無需手動調整順序**。重點在寫出 **SARG 形式** (`欄位 運算子 常數/變數`)，避免函數包裹欄位、`OR`、`NOT IN`、`LIKE '%...%'` 破壞索引使用。