SQL Server 2005 擴充 TOP 功能:支援 UPDATE/DELETE、接受變數/運算式參數,但 不支援直接指定 ORDER BY。若需「更新/刪除前 N 筆依特定排序」,需搭配 **CTE (Common Table Expression)** 實現。
-- SELECT (舊版支援)
SELECT TOP (10) * FROM tb ORDER BY id;
-- UPDATE/DELETE (2005 新增)
UPDATE TOP (2) tb SET tag = 1;
DELETE TOP (2) FROM tb;
-- 參數化
DECLARE @n INT = 3;
SELECT TOP (@n) * FROM tb;
UPDATE TOP (@n) tb SET tag = 1;
UPDATE TOP (2) tb SET tag = 1 ORDER BY id DESC ❌ 語法錯誤。
CTE 內部可用 TOP ... ORDER BY 篩選目標列,外層對 CTE 更新/刪除,**異動會直接反映到底表**。
USE tempdb;
GO
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
CREATE TABLE tb (id INT IDENTITY(1,1), name VARCHAR(10), tag INT DEFAULT 0);
INSERT INTO tb (name) VALUES ('a'),('b'),('c'),('d'),('e');
-- 1. 更新前兩列 (依 ID 遞增,預設順序)
UPDATE TOP (2) tb SET tag = 1;
SELECT * FROM tb; -- id 1,2 tag=1
-- 2. 更新後兩列 (依 ID 遞減) → 需 CTE
;WITH t AS (SELECT TOP (2) * FROM tb ORDER BY id DESC)
UPDATE t SET tag = 1;
SELECT * FROM tb; -- id 4,5 tag=1
-- 3. 刪除前兩列
DELETE TOP (2) FROM tb;
SELECT * FROM tb; -- 剩 id 3,4,5
-- 4. 刪除後兩列 (依 ID 遞減)
;WITH t AS (SELECT TOP (2) * FROM tb ORDER BY id DESC)
DELETE FROM t;
SELECT * FROM tb; -- 剩 id 3
結合 OUTPUT 子句與 UPDATE TOP (1),可實作高併發下的「搶單/任務領取」無鎖模式:
DECLARE @task TABLE (id INT, data VARCHAR(100));
INSERT @task VALUES (1,'A'),(2,'B'),(3,'C');
UPDATE TOP (1) @task
SET data = 'PROCESSING_' + data
OUTPUT inserted.id, inserted.data -- 立即回傳被領取的任務
WHERE data NOT LIKE 'PROCESSING_%';
此模式適用於佇列處理、分散式鎖、秒殺庫存扣減等高併發場景,避免 SELECT ... FOR UPDATE 的鎖爭用。