彙整 SQL Server 2005 開發實戰中四大高頻問題:並發控制、索引新特性、命名規範、CLR 整合誤區。
問題一:預存程序並發下的超賣/超訂問題
場景
公車售票:檢查剩餘座位 → 有空位則插入售票記錄。兩並發交易同時讀到「有空位」,皆插入導致超賣。
CREATE PROCEDURE AddTicket
AS
BEGIN TRAN
DECLARE @FreeSeats INT;
SELECT @FreeSeats = COUNT(*) FROM Tickets WHERE SeatTaken = 0;
IF @FreeSeats > 0
INSERT INTO Tickets ...;
COMMIT;
解決方案比較
| 方案 | 原理 | 優點 | 缺點 |
| SERIALIZABLE 隔離級別 | 範圍鎖防止 Phantom Read | 簡單 | 並發度極低、死鎖高 |
| 應用層鎖 (sp_getapplock) | 邏輯鎖序列化關鍵區 | 粒度可控 | 需應用配合、跨實例無效 |
| 座位表 + 唯一索引 (推薦) | 預先建立所有座位列,唯一索引防重複插入 | 資料庫層保證、高並發、無鎖競爭 | 需預先建模 |
| Service Broker 隊列 | 每座位一訊息,RECEIVE 原子取出 | 原生非同步、保證不重複 | 架構複雜、學習曲線陡 |
最佳實務:座位預建模式
CREATE TABLE BusSeats (
BusId INT, SeatNo INT,
PassengerId INT NULL,
CONSTRAINT PK_BusSeats PRIMARY KEY (BusId, SeatNo),
CONSTRAINT UQ_Passenger UNIQUE (PassengerId) -- 一人一座
);
-- 售票 = 原子更新
UPDATE BusSeats
SET PassengerId = @UserId
WHERE BusId = @BusId AND SeatNo = @SeatNo AND PassengerId IS NULL;
-- @@ROWCOUNT = 1 表示成功
問題二:SQL Server 2005 新索引類別?
- 關聯式表**無新索引類型**,仍為 B-tree 實作的叢集/非叢集索引
- **增強項**:
- 全文檢索重寫 (整合於引擎,支援語意搜尋)
- XML 索引:主要/次要/路徑/屬性/屬性值索引,支援 XQuery
- 空間索引:幾何/地理資料類型
- 線上索引操作 (ONLINE = ON):建立/重建/卸除不鎖定資料表,DBA 不再需半夜維護窗口
問題三:資料表/欄位命名規範
- 長度 1-128 字元
- 首字元:Unicode 字母、`_`、`@`、`#` (特殊意義:`@`=變數, `#`=暫存表, `##`=全域暫存表)
- 後續字元:字母、數字、`_`、`@`、`$`、`#`
- 不可為保留字 (需雙引號/方括號括起)
- 不含空白/特殊符號 (除非括起)
- 建議:PascalCase (TableName)、camelCase (ColumnName)、英文、無縮寫歧義
問題四:不懂 T-SQL 能寫 CLR 預存程序嗎?
- 技術上可行:CLR 內部用 ADO.NET 存取資料,語法同應用層
- 但**無法完全避開 T-SQL**:資料存取最終仍發送 T-SQL 給引擎
- 適用場景:複雜運算、正則表達式、檔案/網路操作、加密、外部 API 呼叫
- 不適用:純集合操作 (JOIN、聚合、篩選) — T-SQL 更優
- 結論:CLR 補足 T-SQL 弱項,非取代。精通 T-SQL 仍是核心競爭力。
深入理解引擎行為,選對工具解決對的問題,是資料庫開發者的進階之路。
copyright ©
萬盛學電腦網 all rights reserved