SQL Server 開發十大常見問題深度解析：從命名規範到並發控制

前言

SQL Server 開發過程中，命名規範、預存程序設計、並發控制、工具選擇、索引策略等問題高頻出現。本文針對十大典型問題提供專家級解答與最佳實踐建議。

1. 表與欄位命名規範

• 長度：1~128 字元。
• 首字元：Unicode 字母、底線 `_`、@、#。
• 後續字元：Unicode 字母、數字、@、$、#、_。
• 特殊前綴含義： `@`=區域變數/參數，`#`=暫存表/程序，`##`=全域暫存物件，`@@`=系統函數 (避免自定義物件用)。
• 不可為保留字；含空格/特殊字元需用 `[]` 或 `""` 括起 (需 QUOTED_IDENTIFIER ON)。

2. 無 T-SQL 撰寫 CLR 預存程序可行性

技術上可行，但無法存取資料。 CLR 預存程序透過 ADO.NET 存取資料，最終仍需發送 T-SQL 指令。適用場景：檔案處理、網路服務呼叫、複雜運算、正則表達式等「非資料存取」邏輯。資料密集型操作仍建議用 T-SQL。

3. CLR vs T-SQL 預存程序效能對比

• T-SQL 勝出： 純資料操作 (CRUD)、集合式邏輯、簡單邏輯 — 優化器成熟、解釋執行開銷低。
• CLR 勝出： 複雜數學運算、字串處理、正則表達式、程序式邏輯、大量迴圈 — 編譯為原生代碼、物件導向庫豐富。
• 鐵律： 在你的資料、你的伺服器、你的負載下實測兩套方案，以數據說話。

4. 在預存程序中使用另一個預存程序的結果集

核心技巧：暫存表 + INSERT EXEC。

CREATE PROC usp_GetAuthors AS SELECT * FROM Authors; -- 來源程序

CREATE PROC usp_ProcessAuthors AS
    CREATE TABLE #TmpAuthors ( ... ); -- 定義結構
    INSERT #TmpAuthors EXEC usp_GetAuthors; -- 捕獲結果集
    SELECT * FROM #TmpAuthors WHERE ...; -- 後續處理
    DROP TABLE #TmpAuthors;

前提：來源程序僅回傳單一結果集。

5. SQL Server 2005 並發問題解決方案

場景：檢查剩餘座位 → 插入售票。高併發下「查詢-插入」非原子導致超賣。

方案 A：唯一約束 + 業務鍵 為每個座位建立唯一識別碼，建表 Bookings(SeatID UNIQUE)。插入衝突自動失敗，應用層捕獲錯誤重試。

方案 B：Service Broker 隊列單一消費者 每班車建立一個會話/隊列，座位作為訊息入隊。RECEIVE 保證單一消費者處理，天然序列化，零並發衝突。

6. SQL Server 2005 取代查詢分析器的工具

SQL Server Management Studio (SSMS)：整合查詢編輯、物件瀏覽、效能報表、原則管理、部署精靈的統一管理介面。

7. SQL 與 T-SQL 差異補充

• SQL：ANSI/ISO 標準查詢語言。
• T-SQL：Microsoft/Sybase 擴充，包含程式控制 (IF/WHILE/TRY-CATCH)、系統函數、DDL 擴充、錯誤處理、暫存表、動態 SQL 等。

8. SQL Server 2005 新索引類型

• 關聯表無新索引類型 (仍為 B-tree 叢集/非叢集)。
• 增強項： 全文檢索索引重寫、XML 索引 (Primary/Secondary/Selective)、線上索引操作 (ONLINE = ON，不鎖表維護)、包含欄位索引 (INCLUDE columns) 實現覆蓋索引。

9. 產生 SELECT 腳本技巧

• SSMS：右鍵資料表 → 指令碼為 → SELECT 陳述式。
• 系統視圖：查詢 sys.columns 動態拼接欄位清單。

10. 列出無資料列的資料表

-- 方法 1: 系統視圖 (快速、近似)
SELECT OBJECT_NAME(object_id) AS TableName
FROM sys.partitions
WHERE index_id IN (0,1) AND rows = 0 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1;

-- 方法 2: 動態 SQL (精確、較慢)
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(schema_name(schema_id)) + '.' + QUOTENAME(name) + ') PRINT ''' + QUOTENAME(schema_name(schema_id)) + '.' + QUOTENAME(name) + ''';'
FROM sys.tables;
EXEC sp_executesql @sql;