您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

升級 SQL Server 2005 的三大核心理由：分區表、ROW_NUMBER、PIVOT

編輯：關於SqlServer

前言

面對龐大資料量與複雜報表需求，SQL Server 2000 架構已顯力不從心。SQL Server 2005 引入三大殺手級功能——資料表分區、ROW_NUMBER()、PIVOT/UNPIVOT——徹底解決效能、開發效率與報表轉換三大痛點。

理由一：資料表分區——大資料量效能救星

SQL 2000 時代的痛苦

手工建立多張同結構表（如 Sales_2023_01, Sales_2023_02...）
建立檢視表 UNION ALL 串聯偽裝成單一表
維護外鍵、檢查約束、觸發器極度繁瑣
跨分區查詢無法利用分區剪枝

SQL 2005 分區表架構

-- 1. 建立分區函數（依日期範圍）
CREATE PARTITION FUNCTION pf_SalesDate (DATETIME)
AS RANGE RIGHT FOR VALUES 
('2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01')

-- 2. 建立分區配置（對應檔案群組）
CREATE PARTITION SCHEME ps_SalesDate
AS PARTITION pf_SalesDate
TO (FG_Jan, FG_Feb, FG_Mar, FG_Apr, FG_Others)

-- 3. 建立分區表
CREATE TABLE Sales (
    SaleID BIGINT IDENTITY,
    SaleDate DATETIME NOT NULL,
    Amount MONEY,
    ...
) ON ps_SalesDate(SaleDate)

核心優勢

功能	手工分表+檢視表	原生分區表
資料移動（歸檔/清除）	INSERT/DELETE 大量資料，鎖定久	`SWITCH PARTITION` 毫秒級元資料操作
查詢最佳化	需手工加 WHERE 條件	最佳化器自動分區剪枝
索引維護	每張表獨立維護	可對齊/非對齊索引，單獨重建分區
約束/關聯性	需在每張表重複定義	單一表定義，自動套用所有分區
備份/還原	檔案群組層級可獨立備份	同支援檔案群組層級備份

實戰：毫秒級歸檔

-- 將 2023 年 Q1 資料從暫存表切換至正式分區表
ALTER TABLE Sales_Staging SWITCH PARTITION 1 TO Sales PARTITION 1
-- 僅更新元資料，零資料移動，零鎖定衝突

理由二：ROW_NUMBER() —— 結束手工加序號的歷史

舊時代：應用層加序號

資料撈回應用程式 → 迴圈加序號 → 繫結 Grid/Report
大量資料記憶體壓力大、網路傳輸量大
分頁查詢更是惡夢

新時代：伺服器端單行解決

SELECT 
    ROW_NUMBER() OVER (ORDER BY SaleDate DESC) AS SeqNo,
    SaleID, CustomerName, Amount
FROM Sales
WHERE SaleDate >= '2023-01-01'

搭配 CTE 即成高效分頁（參考文章 87612）。

理由三：PIVOT/UNPIVOT —— 交叉表報表終結者

經典痛點：考勤/成績單橫向展開

31 天出勤 → 31 欄位？查詢時需動態 SQL 或 31 個 CASE
學生 × 課程矩陣 → 動態欄位數不固定

PIVOT：列轉行（長表變寬表）

-- 原始長表：StudentID, Subject, Score
SELECT StudentID, [數學], [英文], [物理], [化學]
FROM (
    SELECT StudentID, Subject, Score FROM Scores
) AS SourceTable
PIVOT (
    AVG(Score) FOR Subject IN ([數學], [英文], [物理], [化學])
) AS PivotTable

UNPIVOT：行轉列（寬表變長表）

-- 寬表：Product, JanSales, FebSales, MarSales
SELECT Product, Month, Sales
FROM ProductSales
UNPIVOT (
    Sales FOR Month IN (JanSales, FebSales, MarSales)
) AS UnpivotTable

總結比較

功能	SQL 2000 解法	SQL 2005 原生支援	效益
大表管理	手工分表+檢視表	分區表+分區函數/配置	維護成本降 90%，查詢效能增 3-10x
結果集編號/分頁	應用層/臨時表/游標	ROW_NUMBER() + CTE	單一查詢完成，效能最佳化
交叉報表	動態 SQL/CASE 爆炸	PIVOT/UNPIVOT	標準語法，可讀性極佳