萬盛學電腦網

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

SQL Server 檢視表完整指南:定義、優勢、建立與管理實戰

檢視表 是虛擬資料表,本質是儲存的 SELECT 查詢。它不儲存實體資料(索引檢視表除外),卻能像資料表一樣被查詢、甚至更新。本文詳細解析檢視表的定義、用途、建立/修改/刪除語法與進階應用。

一、檢視表定義與本質

  • 虛擬表:不佔用資料儲存空間,僅儲存查詢定義。
  • 動態結果:每次查詢檢視表時,資料庫引擎即時執行底層查詢,回傳最新資料。
  • 邏輯抽象層:隱藏複雜 JOIN、篩選、計算邏輯,對外呈現簡潔介面。

二、檢視表四大核心用途

用途說明實例
資料安全/存取控制僅暴露特定欄位/列,隱藏敏感欄位(如薪資、身分證號)CREATE VIEW v_EmpPublic AS SELECT EmpId, Name, Dept FROM Employee;
簡化複雜查詢封裝多表 JOIN、子查詢、聚合邏輯,應用端只需 SELECT * FROM View多維報表基礎檢視表
邏輯資料獨立性底層結構變更(如拆表、欄位重命名)時,只需修改檢視表,應用程式不受影響重構時的緩衝層
資料彙總/格式化預先計算衍生欄位、格式化輸出FullName = LastName + ' ' + FirstName

三、建立檢視表:語法與範例

CREATE VIEW [schema.]ViewName [(ColumnAlias1, ColumnAlias2, ...)]
[WITH ENCRYPTION | SCHEMABINDING | VIEW_METADATA]
AS
SELECT ColumnList
FROM TableSource
[WHERE Condition]
[GROUP BY ...]
[HAVING ...];

實戰範例:多表關聯檢視表

CREATE VIEW dbo.v_OfficeInfo
AS
SELECT
    A.Name      AS 樓盤名稱,
    B.A_Name    AS 城市,
    C.A_Name    AS 區域,
    D.A_Name    AS 街道
FROM dbo.Office_Info AS A
INNER JOIN dbo.Base_Area AS B ON A.CityId = B.A_ID
INNER JOIN dbo.Base_Area AS C ON A.AreaId = C.A_ID
INNER JOIN dbo.Base_Area AS D ON A.StreetId = D.A_ID;

使用:SELECT * FROM dbo.v_OfficeInfo;

重要選項

  • WITH SCHEMABINDING:綁定底層結構,防止被引用的表/欄位被修改/刪除,**建立索引檢視表的前提**。
  • WITH ENCRYPTION:加密定義文本(可被反編譯)。
  • VIEW_METADATA:讓 ODBC/OLEDB 客戶端將檢視表視為可更新資料表。

四、修改與刪除檢視表

修改:ALTER VIEW

ALTER VIEW dbo.v_Student
AS
SELECT StudentId, Name, ClassName
FROM Students
WHERE IsActive = 1;
  • 保留權限與相依物件(觸發器、索引等)。

刪除:DROP VIEW

IF EXISTS (SELECT * FROM sys.views WHERE name = 'v_OfficeInfo')
    DROP VIEW dbo.v_OfficeInfo;

五、可更新檢視表:條件與限制

符合以下條件的檢視表可直接 INSERT/UPDATE/DELETE:

  • 僅參考**單一基底表**(或經 WITH CHECK OPTION 的可更新檢視表)。
  • 不含 DISTINCT、GROUP BY、HAVING、聚合函數、UNION、衍生欄位、常數欄位。
  • 未使用 TOP(除非搭配 WITH CHECK OPTION)。
  • 更新的欄位必須直接對應基底表欄位,非運算式結果。

WITH CHECK OPTION:確保透過檢視表修改的資料,仍符合檢視表的 WHERE 條件,否則拒絕操作。

六、索引檢視表:將檢視表物質化

-- 前提:WITH SCHEMABINDING
CREATE VIEW dbo.v_SalesSummary
WITH SCHEMABINDING
AS
SELECT Ctiy, YEAR(OrderDate) AS OrderYear, COUNT_BIG(*) AS Cnt
FROM dbo.SalesInfo
GROUP BY Ctiy, YEAR(OrderDate);

-- 建立唯一叢集索引 → 物質化
CREATE UNIQUE CLUSTERED INDEX IX_v_SalesSummary
ON dbo.v_SalesSummary (Ctiy, OrderYear);
  • 查詢時直接讀取索引頁面,不重新計算聚合,**大幅提升報表效能**。
  • 維護成本:基底表異動時同步更新索引檢視表。

七、系統檢視表與中繼資料查詢

-- 列出所有使用者檢視表
SELECT name, definition FROM sys.views;

-- 檢視定義文本
EXEC sp_helptext 'dbo.v_OfficeInfo';

檢視表是資料庫設計中「抽象與封裝」的利器,善用它能讓系統更安全、維護更輕鬆、查詢更高效。

copyright © 萬盛學電腦網 all rights reserved