萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 視圖(View)完全指南:建立、修改、刪除、加密、SCHEMABINDING 與效能考量

視圖是虛擬表,本質是儲存的 SELECT 查詢,不儲存實體資料。本文涵蓋建立/修改/刪除語法、SCHEMABINDING、ENCRYPTION、CHECK OPTION、索引視圖、分頁查詢、權限控制與效能陷阱。

核心概念

  • 虛擬表:查詢時即時執行底層 SQL
  • 不佔用資料儲存空間(除非建立索引視圖)
  • 可簡化複雜查詢、隱藏敏感欄位、提供穩定介面
  • 相同基表可建立多視圖滿足不同業務視角

建立視圖

CREATE VIEW [schema.]view_name [(col_alias, ...)]
[WITH ENCRYPTION]           -- 加密定義文本,防止 VIEW DEFINITION 洩露
[WITH SCHEMABINDING]        -- 綁定結構,防止底層表結構變更破壞視圖
AS
SELECT_statement
[WITH CHECK OPTION];        -- 透過視圖修改資料時,強制符合 WHERE 條件

實戰範例:多表關聯視圖

CREATE VIEW v_OfficeDetail
WITH SCHEMABINDING
AS
SELECT 
    o.Id,
    o.Name AS OfficeName,
    c.A_Name AS CityName,
    a.A_Name AS AreaName,
    s.A_Name AS StreetName
FROM dbo.Office_Info AS o
INNER JOIN dbo.Base_Area AS c ON o.CityId = c.A_ID
INNER JOIN dbo.Base_Area AS a ON o.AreaId = a.A_ID
INNER JOIN dbo.Base_Area AS s ON o.StreetId = s.A_ID;
GO

修改與刪除

-- 修改 (保留權限、相依性)
ALTER VIEW v_OfficeDetail
AS
SELECT ...;  -- 新查詢

-- 刪除 (安全模式)
IF OBJECT_ID('v_OfficeDetail', 'V') IS NOT NULL
    DROP VIEW v_OfficeDetail;

四大進階選項深度解析

選項作用限制/代價建議場景
SCHEMABINDING鎖定參考表結構,禁止 DROP/ALTER 導致視圖失效參考表必須兩部分名;視圖不可 SELECT *;需明確列出欄位核心業務視圖、索引視圖前置條件
ENCRYPTION加密 sys.sysobjvalues 定義文本無法 sp_helptext、OBJECT_DEFINITION、sys.sql_modules 查看含敏感邏輯、第三方部署
CHECK OPTION透過視圖 INSERT/UPDATE 時,強制新/舊資料符合視圖 WHERE僅對可更新視圖有效;巢狀視圖層層檢查資料品質管控、多租戶資料隔離
索引視圖建立叢集索引將視圖實體化嚴格限制:決定性、無外部存取、SET 選項固定、SCHEMABINDING 必須彙總查詢熱點、預計算 Join 結果

可更新視圖條件

僅單一基表、無聚合/GROUP BY/DISTINCT/UNION、無衍生欄位、含主鍵/唯一鍵 → 可直接 INSERT/UPDATE/DELETE 視圖。

分頁查詢應用

-- SQL Server 2012+ 標準語法
SELECT * FROM v_OfficeDetail
ORDER BY OfficeName
OFFSET @PageSize * (@PageNum - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

權限控制

-- 僅授予視圖 SELECT,不授基表權限 → 實現欄位/行級安全
GRANT SELECT ON v_OfficeDetail TO [AppUser];
DENY SELECT ON Office_Info TO [AppUser];

效能陷阱與優化

  • ❌ SELECT * FROM view WHERE col = 'x' → 視圖定義全掃描再篩選
  • ✅ 視圖定義中包含篩選條件,或建立索引視圖
  • ✅ 避免巢狀視圖過深(>3 層),優化器難展開
  • ✅ 大量資料匯出用 BCP/SSIS 直連基表,繞過視圖

監控視圖使用

-- 視圖定義
SELECT OBJECT_DEFINITION(OBJECT_ID('v_OfficeDetail'));

-- 相依物件
EXEC sp_depends 'v_OfficeDetail';

-- 索引視圖空間
SELECT name, type_desc, used_page_count * 8 / 1024.0 AS SizeMB
FROM sys.indexes i
JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.object_id = OBJECT_ID('v_OfficeDetail');

視圖是「邏輯層封裝」的利器:善用 SCHEMABINDING 守護穩定、CHECK OPTION 守護品質、索引視圖加速彙總。但切記:視圖不是萬能藥,複雜巢狀視圖會讓優化器「放棄思考」,關鍵路徑仍需直接操作基表或實體化表。

copyright © 萬盛學電腦網 all rights reserved