Microsoft SQL Server 2000 超級管理手冊(十八)

18. 檢視表的建立與使用

何謂檢視表

檢視表概念

建立檢視表

檢視表的修改與刪除

SQL Server 2000 中檢視表的增強功能

本章總結

在 第17章 中，我們學習了索引，索引是一種資料庫輔助結構，獨立於資料庫的資料結構，但是卻與存取資料庫的資料有關。換句話說，索引是一種獨立結構，但是和資料整合性地連結在一起。下面我們將學習另一種資料庫輔助結構：檢視表。和索引一樣，檢視表和資料分開存在，使用時才和資料作連結。檢視表在使用者存取資料前，先將資料篩選或處理。本章中將詳細學習什麼是檢視表、檢視表與資料的關聯性，檢視表的使用、建立和管理。另外還會看看檢視表在Microsoft SQL Server 2000的新增功能。

何謂檢視表
　

您可以將 檢視表 （view）想像成是虛擬資料表，由SELECT陳述式查詢的結果集組成。對使用者來說，檢視表看起來就像一般的資料表，但是其中所包含的資料可能來自不同資料表查詢後所得來的結果。事實上，使用檢視表的方法和使用資料表可以說是一樣的，使用者可以在T-SQL陳述式中引用檢視表，方法和引用資料表相同，如SELECT、INSERT、UPDATE和DELETE都可以在檢視表中操作。

實際上，檢視表為被預先定義的SQL陳述式。當存取檢視表時，SQL Server Query Optimizer就合併執行查詢陳述式與預先定義檢視表的SQL陳述式。

使用檢視表的優點在於，不用複製資料就可以建立具有不同屬性的檢視表。檢視表在很多情形下是很有用的。在本章後面我們會看到，檢視表可用於保障資料的安全性、呈現查詢結果的簡易性，表達資料呈現的邏輯性。此外，也可以使用檢視表來合併已經分割的資料。

檢視表概念
　

本節中會學習不同類型的檢視表，以及檢視表的使用優點與限制。

檢視表類型
　

不同類型的檢視表可依使用狀況，建立下面任何一種形式的檢視：

•  資料表中資料行的子集 檢視表僅包括特定的資料行。可能是最普遍的檢視表類型，欲顯示簡化後的資料，或基於安全性的考量時使用。
  　
•  資料表中資料列的子集 檢視表僅包括特定的資料列，亦可以基於安全性的理由使用。
  　
•  聯結多個資料表 您可以藉由聯結操作建立檢視表，使用檢視表可以簡化繁雜的聯結操作。
  　

•  彙總資訊 檢視表僅顯示彙總後的資料，簡化複雜的操作過程。
  　

  在本章稍後的 〈使用T-SQL建立檢視表〉 一節中會介紹這些類型檢視表不同的呈現方式。

  檢視表也可用於合併分割的資料，基於管理上的方便，大型的資料表可能被分割為數個小型的資料表。使用者可依需要建立檢視表，使數個小型資料表合併在一個大型的虛擬資料表上。

  檢視表的優點
  　

  使用檢視表的第一個優點是，檢視表所呈現的資料永遠是即時資料。因為檢視表由SELECT陳述式定義，因此無論何時存取檢視表，都會執行SELECT陳述式，也就是當要求資料時才執行查詢。因此，儘管底層的資料表中的資料可能會變更，但檢視表呈現的永遠為最新資料。

  使用檢視表的第二個優點是，檢視表具有和底層資料表不同的安全性層級。因為定義檢視表的查詢是依使用建立時的安全性層級執行。因此，檢視表可以隱藏不希望被某一層級使用者看到的資料。在本章 〈資料行的子集〉 一節中會再討論這個功能。

  檢視表的限制
  　

  SQL Server對檢視表的建立和使用有一些限制，限制如下：

•  資料行限制 檢視表最多只能引用1024個資料行。如果超過這個限制，就要利用其他方法引用。
  　
•  資料庫限制 檢視表只能在現行存取資料庫的資料表中建立。
  　
•  安全性限制 檢視表的建立者必須擁有存取檢視表所引用所有資料行的權限。
  　
•  資料完整性規則 任何更新、修改等操作都不能破壞資料的完整性規則。例如，如果底層資料表不允許NULL值，那麼檢視也不允許NULL值。
  　
•  巢狀檢視表層級限制 檢視表可以建立在其他檢視表之上。換句話說，可以建立一個可存取其他檢視表的檢視表，檢視表的巢狀層次最高可達32層。
  　
•  SELECT陳述式限制 檢視表的SELECT陳述式不能包括ORDER BY、COMPUTE、COMPUTE BY陳述式及INTO關鍵字。
  　

  ---

  說明

  關於檢視表的其他限制，可在《線上叢書》索引中輸入『建立檢視表』，並進入 建立檢視表 主題。

  ---

  建立檢視表
  　

  同索引一樣，可利用不同的方法建立檢視表。如果在日後會建立更多的檢視表，建議利用CREATE VIEW陳述式（T-SQL陳述式）建立檢視表，因為這樣可以將T-SQL陳述式儲存在指令碼中，當要再建立檢視表時再重複叫出指令碼來修改使用。其他建立檢視表的方法為，使用SQL-Server Enterprise Manager和建立檢視表精靈。

  與索引和其他多數操作一樣，如果將來要建立多個檢視表，建議使用T-SQL命令。在指令碼中輸入T-SQL敘述，以後便可以多次編輯和使用檔案。如果瞭解所要建立的檢視，還可以使用SQL Server Enterprise Manager簡單地建立檢視。最後，您可以使用建立檢視表精靈瀏覽建立檢視表的過程，不管是初學者或是專家，建立檢視表精靈都一樣有用。

  使用T-SQL建立檢視表
  　

  使用T-SQL建立資料表的步驟很簡單：使用ISQL、OSQL或SQL Server Query Analyzer，並執行CREAT VIEW建立來檢視表。如之前所述，您可以將T-SQL命令存入指令碼中，待日後欲使用時呼叫出來修改使用即可（請您記得將資料庫定義一起存入指令碼，以便日後重建資料庫時使用）。

  以下為CREATE VIEW命令的語法：

  CREATE VIEW view_name [(column, column, ...)]
  [WITH ENCRYPTION]
  AS
  your SELECT statement
  [WITH CHECK OPTION]

  建立檢視表時，您可以啟動兩個選項更改檢視表的行為的選項。一個選項為使用WITH ENCRYPTION關鍵字，另一個選項為使用WITH CHECK OPTION，或是兩者一起使用也可以。現在讓我們來仔細研究這幾個選項。

  WITH ENCRYPTION關鍵字會加密檢視表的定義。SQL Server使用的加密方式和密碼的方式相同。這種安全機制令特定使用層級的使用者無法得知哪些資料表正在被存取。

  WITH CHECK OPTION關鍵字指定對檢視表的任何資料修改陳述式都必須遵守定義檢視表的SELECT陳述式中所設的基準。舉例來說，如果在檢視表上建立一個資料列，而該資料列無法在檢視表上顯示，這種修改就不允許。假設將一個檢視表定義為「選取所有財務部門員工相關資訊」，通常情況下，如果您沒有指定WITH CHECK OPTION選項，您就可以將「部門」資料行的值從「財務」改為其他部門。如果指定了WITH CHECK OPTION，則不允許做這種修改，因為變更任一資料列的「部門」資料行的值，您將無法透過檢視表再存取該資料列。WITH CHECK OPTION關鍵字可指定不可以在檢視表中執行會導致無法在檢視表中存取某列的變更。

  只要修改檢視表中SELECT陳述式的定義，您就可以建立任何您所需要的檢視表，例如選擇資料行或資料列的子集，或是聯結操作。下面將學習如何使用T-SQL建立不同類型的檢視表。

  資料行子集
  　

  由資料行子集所構成的檢視表可為您欲公開的資料提供某種程度的安全性。現在看看以下範例。假設一個公司內部資料庫中，有一個命名為Employee的資料表，所包含的資料行如圖18-1所示。

  
  　

  圖18-1 Employee資料表

  資料表中的大部分資料都屬於私人資料，不能被其他員工看到。不過，某些資料卻必須讓一般員工檢視。這裡的重點在於建立一個可以允許所有員工存取特定資料的檢視表，即可解決問題，另外，檢視表也可以避免資料庫中其他的資料表含有重複的員工資料。

  使用下面的T-SQL陳述式，為Employee資料表建立可以存取name、phone和office資料行的檢視表：

  CREATE VIEW emp_vw
  AS
    SELECT  name, 
            phone, 
            office
    FROM    Employee

  所建立的檢視表所包括的資料行如圖18-2所示。儘管所有的資料皆存在於底層資料表，但透過檢視表存取資料的使用者只能看到檢視表中所選取的資料行。由於檢視表可以擁有和底層資料表不同的安全性層級，所以雖然檢視表允許任何人存取，但底層資料表仍然是安全的。換句話說，可以只允許人力資源部門存取Employee資料表的所有資料，而其他部門員工只能使用檢視表。

  資料列子集
  　

  由資料列子集構成的檢視表可以限制使用者能存取的資料列。假設Employee資料表填入了資料，如圖18-3所示。在本例中，我們不在資料行設限制，而是透過WHERE陳述式來限制資料列，如下所示：

  
  　

  圖18-2　emp_vw檢視表

  CREATE VIEW emp_vw2
  AS
     SELECT  *
     FROM    Employee
     WHERE   Dept = 1

  
  　

  圖18-3 包含資料的Employee資料表

  檢視表的結果只顯示在部門1工作的員工的資料列，如圖18-4所示。假設人力資源部門必須只存取某部門內部員工的記錄，這個檢視表便很有用。如同由資料行子集構成的檢視表一樣，由資料列子集構成的檢視表也可分配與資料庫不同的安全性層級。

  
  　

  圖18-4 emp_vw2檢視表

  聯結
  　

  透過在檢視表中定義聯結，實際的JOIN陳述式對使用者來說是隱藏的，這簡化了存取資料的T-SQL陳述式。舉例來說，有兩張資料表各命名為Manager和 Employee2，如圖18-5所示。

  
  　

  圖18-5 Manager和Employee2資料表

  使用下面的陳述式將兩個資料表聯結到一個虛擬的資料表上：

  CREATE VIEW org_chart
  AS
     SELECT   Employee2.ename, Manager.mname
     FROM     Employee2, Manager
     WHERE    Employee2.manager_id = manager.id
     GROUP BY Manager.mname, Employee2.ename

  在本範例中，兩個資料表透過manager_id值聯結。最後的結果依經理的名稱分組，顯示在org_chart檢視表，如圖18-6所示。注意，如果在Manager資料表中，某個經理之下所屬員工未被列入Employee2資料表，檢視表就不會出現該經理的記錄項目。如果Employee2資料表中的某個員工，其所屬經理未被列在Manager資料表，檢視表中也不會有記錄項目。對於一般使用者來說，看到的是一份由員工和其所屬經理構成的虛擬資料表。

  
  　

  圖18-6 org_chart檢視表

  彙總
  　

  彙總（aggregation）檢視表可用在很多方面，如檢視部門的平均值、總和等等。舉例來說，要編制預算，可利用彙總檢視表檢視公司各部門薪資狀況。使用T-SQL查詢也可以達成這項任務。使用檢視表的優點在於，使用者可簡單執行檢視，而無須了解彙總及T-SQL的執行與運算細節。

  ---

  說明

  SQL Server彙總函數對一組值進行運算後傳回單一值。彙總函數包括AVG、COUNT、MAX、MIN和SUM。

  ---

  下列的陳述式利用彙總函數（SUM），計算Employee資料表的總和：

  CREATE VIEW sal_vw
  AS
     SELECT    dept, 
               SUM (Salary) AS [SUM(salary)]
     FROM      Employee
     GROUP BY  dept

  在本例中，檢視表建立一個虛擬的資料表，顯示各部門的薪資的總合。最後的資料是按部門分類，如圖18-7所示。這個彙總檢視表是比較簡單的。事實上，檢視表可以依實際需要執行更複雜的功能。

  
  　

  圖18-7 sal_vw檢視表

  合併分割的資料表
  　

  檢視表通常也用於合併分割的資料表，使其成為單一的大型虛擬資料表。通常我們會利用分割資料表的方式，減少資料表和索引的大小。要分割資料表，要先建立數個小型資料表來取代原來的大型資料表，然後每個小型資料表再從原大型資料表分配特定範圍的值。舉例來說，一個公司內部可建立許多小型資料表，每一個小型資料表皆含有該星期的銷售記錄，若要檢視該公司的銷售歷史記錄，就利用檢視表合併這幾個小型資料表，而無需在一開始就將所有銷售記錄輸入成為一份大型資料表。

  圖18-8所顯示的檢視表，在使用者看起來，就像是一份大型資料表內的資料，其實該資料表是數個小型資料表的結合，且每一個資料表都有自己的索引（事實上，叢集的日期索引在這裡更適用）。

  
  　

  圖18-8 使用檢視表合併分割的資料表資料

  如之前所述，分割的資料表為DBA建立了更易於管理的系統，而合併分割的資料表則將資料以簡化的方式呈現給使用者。

  要建立一個合併分割資料的檢視表，首先要先建立分割的資料表。這些資料表可能包含了銷售資料。每一個資料表會將資料儲存一個特定的週期（約一個星期或一個月）。當這些資料表建立完成，可以使用UNION ALL陳述式建立一個包含所有資料的檢視表。舉例來說，假設有四個資料表，分別命名為table_1、table_2、table_3和table_4，以下的陳述式可用於建立一個包含所有資料的大型虛擬資料表：

  CREATE VIEW partview
  AS
     SELECT * FROM table_1
     UNION ALL
     SELECT * FROM table_2
     UNION ALL
     SELECT * FROM table_3
     UNION_ALL
     SELECT * FROM table_4

  現在所有的資料都在同一個資料表（即為檢視表）中，並且易於管理。然而如果您建立新分割資料表且刪除舊分割資料表，則您就必須重建檢視表。

  使用Enterprise Manager建立檢視表
  　

  本節我們會使用Enterprise Manager在Northwind資料庫中建立一個檢視表，步驟如下：

  2. 在Enterprise Manager視窗中，展開 資料庫 資料夾，按一下Northwind資料庫，如圖18-9所示。
     　

     圖18-9 顯示Northwind資料庫資訊

  3. 在Northwind資料庫上按右鈕以叫出快顯功能表。選擇 新增 / 檢視表 以進入 新增檢視表 視窗，如圖18-10所示。利用該視窗定義檢視表名稱，檢視表所使用的資料行，以及底層資料表。

      新增檢視表 視窗包含下列四個窗格：

     • •  圖表窗格 顯示用來建立檢視表的資料表資料。資料行可在此窗格中選取。
         　
     •  方格窗格 顯示從底層資料表中所選擇構成檢視表的資料行。資料行可在此窗格中選取。
       
       　

       圖18-10 新檢視表視窗

     •  SQL窗格 顯示用於定義檢視表的SQL陳述式。當您在圖表窗格中拖曳資料或在方格窗格中選擇資料行時，SQL Server就會產生SQL陳述式，可以在SQL窗格中檢視剛產生的SQL陳述式。
       　
     •  結果窗格 顯示從檢視表中檢索到的資料列，提供資料應顯示的狀況。
       　

     在 新增檢視表 視窗的工具列點選相關的圖示，可以顯示或隱藏某個窗格，工具列上的其他窗格也提供重要的選項，以下我們由左到右解釋這些工具列上的其他選項：

     • •  存檔 儲存檢視表。
         　
       •  屬性 可更改檢視表的屬性。按一下這個圖示以顯示 屬性 視窗，在這裡可設定 重複資料僅顯示一筆 及 加密檢視 選項。
         　
       •  顯示/隱藏窗格 （四個圖示）　可選擇顯示或隱藏 新增檢視表 視窗的任意四個窗格。
         　
       •  執行 執行及顯示在 結果窗格 內的查詢，可驗證查詢是否被正確執行。
         　
       •  取消執行並清除結果 清除 結果窗格 。
         　
       •  驗證SQL 針對底層資料表查詢和驗證SQL陳述式是否正確。
         　
       •  移除篩選 移除已定義的篩選條件。
         　
       •  使用 'GROUP BY' 在SQL窗格陳述式中新增GROUP BY子句。
         　
       •  加入資料表 可為查詢新增資料表。
         　
  4. 修改 SQL窗格 中的SELECT陳述式，以符合圖18-11所示的S ELECT陳述式。這個檢視表由CompanyName、ContactName和Phone資料行構成。輸入SELECT陳述式後，按一下 驗證SQL 按鈕檢測查詢是否有效。如果檢測通過，在接下來的對話方塊中按一下 確定 ，允許Enterprise Manager將資料填入 圖表窗格 和 方格窗格 。 新增檢視表 視窗將如圖18-11所示。
     　

     圖18-11 填入資料後的「新增檢視表」視窗

  5. 利用 結果窗格 ，確認檢視表依照預期執行後，關閉 新增檢視表 視窗。這時會出現對話方塊，詢問是否要儲存檢視表。若按一下 確定 ，會提示您為檢視表選取名稱。請輸入一個描述性的名稱，按一下 確定 儲存以上的操作。

  現在檢視表可以使用了。您可以利用Enterprise Manager設定新檢視表的屬性，包括設定權限。在本章後面的 〈檢視表的修改與刪除〉 一節將仔細介紹檢視表屬性視窗。

  使用建立檢視表精靈
  　

  請參照下列步驟使用建立檢視表精靈：

  1. • 在Enterprise Manager中，從 工具 功能表中選取 精靈 ，展開 資料庫 ，選擇 建立檢視表精靈 ，然後按一下 確定 ，進入 歡迎使用建立檢視表精靈 ，如圖18-12所示。
  2. 按一下 下一步 顯示 選取資料庫 視窗，在這裡可以選擇要建立檢視表的資料庫，本例中為Northwind資料庫。
     　

     圖18-12 「歡迎使用建立檢視表精靈」視窗

  3. 按一下 下一步 進入 選取物件 視窗，如圖18-13所示。這裡可選擇檢視表所要引用的一個或多個資料表。假設建立的是一個簡單的檢視表，選擇單一資料表即可。假設要聯結建立檢視表，就選擇多個資料表。
     　

     圖18-13 「選取物件」視窗

  4. 按一下 下一步 進入 選取資料行 ，如圖18-14所示。這裡可以選擇檢視表中所要用的到資料行，本例中要用到的是CompanyName、ContactName和Phone資料行。
     　

     圖18-14 「選取資料行」視窗

  5. 按一下 下一步 進入 定義限制 視窗，這個視窗可依需要定義WHERE子句，限制檢視表所選擇的資料列。
  6. 按一下 下一步 進入 為檢視表命名 ，如圖18-15所示。在文字方塊中輸入檢視表的名稱。
  7. 按一下 下一步 進入 完成建立檢視表精靈 視窗，如圖18-16所示。在這裡可以按一下 完成 儲存檢視表，或是按一下 上一步 更改設定，也可以按一下 取消 放棄建立檢視表。
     　

     圖18-15 「為檢視表命名」視窗

     
     　

     圖18-16 「完成建立檢視表精靈」視窗

  使用檢視表秘訣
  　

  在建立檢視表時，請記住檢視表是由存取底層資料的SQL陳述式所構成。遵循以下的使用原則，可以改善資料庫的執行效能：

•  利用檢視表提供的安全機制 檢視表可讓使用者只透過檢視表存取資料，無法直接存取檢視表的的底層資料表，所以沒有必要的資料就不會出現在檢視表上。這同時還能增加安全性，因為使用者只能看到檢視表中定義的資料，而看不到底層資料表中的資料。當使用者僅允許存取資料表的部分資訊時，也無需重新建立新的資料表供此用途，因此也不會增加資料庫負擔。
  　
•  善用索引 由於使用檢視表時是存取底層資料表的資料，包括資料行的所設定的索引。如果資料表有一個資料行設為索引，請定義檢視表的SELECT陳述式中的WHERE子句包括這一個資料行。只有當該資料行是檢視表的一部分，且在WHERE子句中使用時，才會利用該索引。舉例來說，若在Employee資料表的Dept資料行上建立了索引，而且該資料行包括在檢視表中，那麼就可以在檢視表中使用索引。
  　
•  將資料進行分割 檢視表可用來分割資料。分割資料的好處在於減少花在建立索引的時間，並透過減少獨立元件所佔用的空間，達到管理虛擬資料表的目的。舉例來說，將資料分割成幾個小型的資料表後再重建索引，比起在一個大型資料表重建索引所需的時間還要來得短。所以可以以定義檢視表的方式，清楚地將每個資料表合併成一個大型資料表，這個方法對於儲存歷史資料的大型資料表尤其實用。
  　

  檢視表的修改與刪除
  　

  利用Enterprise Manager或T-SQL命令可修改檢視表。使用Enterprise Manager較為簡單，但使用T-SQL命令的好處在於建立一次命令後可以重複使用。兩種方法都會在本節中示範。

  使用Enterprise Manager修改或刪除檢視表
  　

  請參照以下步驟修改或刪除檢視表：

  2. 在Enterprise Manager中，在選定的伺服器中展開 資料庫 資料夾。展開檢視表所在的資料庫後，按一下 檢視表 圖示以在右方窗格中顯示所有的檢視表，如圖18-17所示。
     　

     圖18-17 點選檢視表後的Enterprise Manager視窗

  3. 在欲修改或刪除的檢視表名稱後按右鈕，會出現快顯功能表，如圖18-18所示，選擇 刪除 即可刪除該檢視表。如要修改檢視表，則選擇 設計檢視 。
     　

     圖18-18 檢視表的快顯功能表

  4. 如果選擇的是 刪除 ，會進入 卸除物件 對話方塊，如圖18-19所示。按一下 顯示依存的情況 按鈕，即可看到檢視表的底層架構，並可看到檢視表和資料表的相依性。如果選取的檢視表是屬於聯結（JOIN）或是聯合（UNION）檢視表，所有相關的資料表都會在這裡顯示；如果選擇的檢視表是資料行或資料列檢視表，在這裡就只會看到一個資料表。當確定要刪除該檢視表，按一下 卸除全部 即可執行刪除工作。
     　

     圖18-19 「卸除物件」對話方塊

     如果選擇的是 設計檢視 ，會出現如圖18-20的 設計檢視表 對話方塊。這個對話方塊和在之前圖18-10所看到的 新增檢視表 視窗類似，利用這個對話方塊修改檢視表的方式就如同之前建立檢視表的方法一樣。

     
     　

     圖18-20 「設計檢視表」對話方塊

  5. 修改完成後，按一下 關閉 按鈕結束視窗，系統會提示您儲存已修改的檢視表。

  當修改完成，可以設定檢視表的權限。先開啟檢視表屬性視窗（在Enterprise Manager中點選檢視表名稱後按右鈕，在快顯功能表中選擇 內容 ），然後按一下 權限 進入權限視窗修改檢視表權限，詳細的設定方式會在本書 第34章 介紹。

  如您所視，使用Enterprise Manager修改檢視表非常容易，但是，如果所要修改或刪除的檢視表是較為大型的檢視表，使用T-SQL會較為方便，因為使用T-SQL可以將T-SQL陳述式儲存為指令碼。

  使用T-SQL修改與刪除檢視表
  　

  利用ALTER VIEW命令可以修改檢視表。ALTER VIEW命令和CREATE VIEW命令的使用方法類似，語法如下：

  ALTER VIEW view_name [(column, column, ...)]
  [WITH ENCRYPTION]
  AS
  your SELECT statement
  [WITH CHECK OPTION]

  ALTER VIEW命令和CREATE VIEW命令之間的區別是，如果檢視表已經存在，CREATE VIEW命令無法執行；如果指定的檢視表不存在，ALTER VIEW命令無法執行。（WITH ENCRYPTION和WITH CHECK OPTION這兩個關鍵字可選擇性的使用，方法在本章之前的 〈使用T-SQL建立檢視表〉 一節已介紹過。）

  現在讓我們回到之前合併分割的資料表範例，實際執行ALTER VIEW命令（回到本章之前 〈合併分割的資料表〉 一節）。我們會在刪除分割後再新增分割，示範如何使用ALTER VIEW命令：

  ALTER VIEW partview
  AS
     SELECT * FROM table_2
     UNION ALL
     SELECT * FROM table_3
     UNION ALL
     SELECT * FROM table_4
     UNION ALL
     SELECT * FROM table_5

  修改過後的檢視表看起來和執行ALTER VIEW命令之前的檢視表相同，但其實已使用ALTER VIEW選擇了不同的資料集。現在檢視表引用的是table_5的資料，不再使用table_1的資料。

  若要刪除檢視表，可使用DROP VIEW命令，語法如下：

  DROP VIEW view_name

  SQL Server 2000中檢視表的增強功能
  　

  SQL Server 2000在檢視表方面有兩項改良：可更新的分散式分割檢視表；以及檢視表中可以建立索引。以下就來看看這兩項強化功能。

  可更新的分散式分割檢視表
  　

  在SQL Server 7以及較早的版本，檢視表所呈現的資料為底層資料表的實際狀態，屬於靜態資料。但在SQL Server 2000，更新分割檢視表的同時會更新檢視表及底層資料表。另外，分割檢視表可以橫跨多個SQL Server 2000系統。分割檢視表可以用於建立資料庫伺服器聯盟。 聯盟 （Federation）是獨立管理，但是卻協同運作來分散系統處理負載的伺服器群組。藉由分割資料來形成資料庫伺服器聯盟，讓您可以擴充系統。資料庫伺服器聯盟可支援大型的電子商務Web網站或是企業型的資料庫系統。圖18-21為一個資料庫伺服器聯盟的設定範例。

  
  　

  圖18-21 SQL Server系統聯盟

  在您建置分割檢視表前，必須先將資料表水平分割。原始資料表會被數個較小型的成員資料表所取代，每一個成員資料表與原始資料表具有相同數量的資料行，並且每一個資料行與原始資料表中的對應資料行具有相同的屬性（例如資料型別、大小、定序）。如果建立的是分散式的資料分割檢視表，每一個成員資料表便位於個別的成員伺服器上。為了可以輕易的識別成員資料表的位置，成員資料庫的成員資料表在每一個成員伺服器上的名稱應該相同，雖然這並不是必要條件，但可以方便系統管理。

  當您設計了成員資料表之後，讓每一個資料表依照某一資料範圍（水平切割）來儲存原始資料。每一個成員資料表中值的範圍是由分割資料行的CHECK條件約束強制限定，並且範圍不得重疊。現在就來看一個水平分割的範例。本範例中我們將Customer資料表分割成四個資料表，這三個資料表會放在不同的伺服器中，每個伺服器上包含3000筆Customer資料表的記錄。約束條件包含在以下的CREATE TABLE陳述式內：

  Server 1:
  CREATE TABLE Customer_Table_1
      (CustomerID      INTEGER PRIMARY KEY
                       CHECK (CustomerID BETWEEN 1 AND 3000),
    .
    .        (Additional column definitions)
    .
  -- 在Server 2:
  CREATE TABLE Customer_Table_2
      (CustomerID      INTEGER PRIMARY KEY
                       CHECK (CustomerID BETWEEN 3001 AND 6000),
    .
    .       (Additional column definitions)
    .
  
  -- 在Server 3:
  CREATE TABLE Customer_Table_3
      (CustomerID      INTEGER PRIMARY KEY
                       CHECK (CustomerID BETWEEN 6001 AND 9000),
    .
    .        (Additional column definitions)
    . 
  
  -- 在Server 4:
  CREATE TABLE Customer_Table_4
      (CustomerID      INTEGER PRIMARY KEY
                       CHECK (CustomerID BETWEEN 9001 AND 12000),
    .
    .        (Additional column definitions)
    .

  建立成員資料表之後，在每個成員伺服器上定義一個分散式的分割檢視表。所有的檢視表應具有相同的名稱以方便應用程式的撰寫，分散式分割檢視表使得執行於任何成員伺服器上的查詢就好像所參考的資料位於本機一樣。換句話說，如果在成員伺服器上所執行的查詢引用的是在其他成員伺服器上的資料，資料仍然立即傳回，就像位於本機一樣。

  為了達成資料在各個成員伺服器之間透通的目的，必須在每一個成員伺服器上新增連結伺服器定義，提供聯盟中各成員伺服器所需的連線資訊，使分散式的分割檢視表可以存取其他伺服器上的資料。您可以使用Enterprise Manager或T-SQL命令建立連結伺服器定義。

  使用T-SQL設定連結伺服器
  　

  以下為使用T-SQL命令建立連結伺服器定義的語法：

  sp_addlinkedserver [@server = ] 'server'
                       [,[@srvproduct = ] 'product_name']
                       [,[@provider = ] 'provider_name']
                       [,[@datasrc = ] 'data_source']
                       [,[@location =] 'location']
                       [,[@provstr = ] 'provider_string']
                       [,[@catalog = ] 'catalog']

  sp_addlinkedserver這個預存程序可包含下列引數：

• @server 連結伺服器名稱。若SQL Server具有多重執行個體，就必須將名稱設為這樣的型式server_name/instance_name。
  　
• @srvproduct OLE DB Provider的產品名稱。如果SQL Server 2000系統是連結到另一個SQL Server 2000系統上，則不必指定 @srvproduct。
  　
• @provider 之前在 @srvproduct中所指定的OLE DB Provider之唯一程式化識別碼。如果是兩個SQL Server 2000系統作連結，則不必指定 @provider。
  　
• @datasrc 是指由OLE DB Provider所解譯的資料來源名稱。如果是兩個SQL Server 2000作連結，則不必指定 @datasrc，但若是要連結到該連結伺服器特定的執行個體，則還是要指定 @datasrc，設定的形式為 server_name/instance_name。
  　
• @location 是指由OLE DB Provider所解譯的資料庫位置。如果是兩個SQL Server 2000系統作連結，則不必指定 @location。
  　
• @provstr 特定OLE DB Provider的特定連接字串。如果是兩個SQL Server 2000系統作連結，則不必指定 @provstr。
  　
• @catalog 是指連線至OLE DB Provider時所使用的資料庫目錄。
  　

  舉例來說，以下的T-SQL命令會建立連結伺服器定義，使四台伺服器－1、2、3和4之間可以互相溝通。

   Server 1 :

  sp_addlinkedserver 'Server2'
  sp_setnetname 'Server2','sql-server-02'
  sp_addlinkedserverlogin SServer2, 'false','sa','sa'
  sp_addlinkedserver 'Server3'
  sp_setnetname 'Server3', 'sql-server-03'
  sp_addlinkedserverlogin SServer3, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server4'
  sp_setnetname 'Server4', 'sql-server-04'
  sp_addlinkedserverlogin SServer4, 'false', 'sa', 'sa'

   Server 2 :

  sp_addlinkedserver 'Server1'
  sp_setnetname 'Server1', 'sql-server-01'
  sp_addlinkedserverlogin SServer1, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server3'
  sp_setnetname 'Server3', 'sql-server-03'
  sp_addlinkedserverlogin SServer3, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server4'
  sp_setnetname 'Server4', 'sql-server-04'
  sp_addlinkedserverlogin SServer4, 'false', 'sa', 'sa'

   Server 3 :

  sp_addlinkedserver 'Server1'
  sp_setnetname 'Server1', 'sql-server-01'
  sp_addlinkedserverlogin Server1, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server2'
  sp_setnetname 'Server2', 'sql-server-02'
  sp_addlinkedserverlogin Server2, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server4'
  sp_setnetname 'Server4', 'sql-server-04'
  sp_addlinkedserverlogin SServer4, 'false', 'sa', 'sa'

   Server 4 :

  sp_addlinkedserver 'Server1'
  sp_setnetname 'Server1', 'sql-server-01'
  sp_addlinkedserverlogin SServer1, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server2'
  sp_setnetname 'Server2', 'sql-server-02'
  sp_addlinkedserverlogin SServer2, 'false', 'sa', 'sa'
  sp_addlinkedserver 'Server3'
  sp_setnetname 'Server3', 'sql-server-03'
  sp_addlinkedserverlogin SServer3, 'false', 'sa', 'sa'

  除了使用sp_addlinkedserver之外，我們還使用了其他兩個陳述式，一個為 sp_setnetname；另一個為sp_addlinkedsrvlogin。這兩個陳述式可輔助分散式分割檢視表的處理。透過呼叫sp_setnetname陳述式可以將在某SQL Server中的連結伺服器名稱和該SQL Server的網路名稱作連結。在之前的範例中，連結伺服器名稱為Server 2的就是在網路上名稱為sql-server-02的伺服器中，並指定登入到連結伺服器上所需的資訊。透過呼叫sp_addlinkedsrvlogin陳述式可指定SQL Server在存取連結伺服器時的使用者身份和密碼。

  使用Enterprise Manager連結伺服器
  　

  利用以下的步驟可透過Enterprise Manager連結伺服器：

  2. 在Enterprise Manager中，在伺服器中展開 安全性 資料夾，如圖18-22所示。
     　

     圖18-22 展開伺服器後的「安全性」資料夾

  3. 在左方窗格中的 連結伺服器 圖示上按右鈕叫出快顯功能表。選擇 新增連結伺服器 進入 連結伺服器屬性 視窗，如圖18-23所示。
     　

     圖18-23 「連結伺服器屬性」的「一般」標籤頁

  4. 在 連結的伺服器 的文字方塊中，輸入想要連結的伺服器名稱，在 伺服器類型 中按一下 SQL Server ，如圖18-24所示。
  5. 按一下 安全性 標籤頁。輸入 本機登入 名稱，選取 模擬 或是輸入 遠端使用者 和 遠端密碼 。圖18-25顯示輸入本機登入名稱後的視窗。
  6. 按一下 確定 完成連結伺服器定義的設定。

  設定完成後就可使用連結伺服器，利用Enterprise Manager修改或刪除連結伺服器的屬性。此外，也可以利用Enterprise Manager檢視在連結伺服器上的資料表和檢視表。

  
  　

  圖18-24 選擇連結伺服器類型

  
  　

  圖18-25 連結伺服器視窗的安全性頁籤

  建立檢視表
  　

  在完成所有連結伺服器定義後，可以建立實際的檢視表。以下的範例會帶您建立一個名稱為sales的檢視表，且該檢視表結合了其他四個伺服器上的sales資料表。

  CREATE VIEW sales
  AS
    SELECT * FROM Server1.bicycle.dbo.sales
      UNION ALL
        SELECT * FROM Server2.bicycle.dbo.sales
      UNION ALL
        SELECT * FROM Server3.bicycle.dbo.sales
      UNION ALL
        SELECT * FROM Server4.bicycle.dbo.sales
  GO

  索引檢視表
  　

  SQL Server 2000允許您在檢視表上建立索引。由於檢視表其實是一個虛擬資料表，所以它的外觀和實際的資料表沒什麼不同。在檢視表上建立索引的T-SQL陳述式跟建立資料表索引的一樣，也就是CREATE INDEX陳述式（在本書 第17章 有介紹）。當然，在設定資料表時我們所輸入的是資料表的名稱，但在設定檢視表的時候就要輸入檢視表的名稱，這是唯一不同的地方。現在，我們就利用T-SQL命令，在名稱為partview的檢視表上建立叢集索引：

  CREATE UNIQUE CLUSTERED INDEX partview_cluidx
     ON partview (part_num ASC)
     WITH FILLFACTOR=95
  ON partfilegroup

  在檢視表上建立索引對系統的執行效能有幾個影響。最明顯當然是改善在檢視表上存取資料時的執行效能，這和之前所提到索引可以改善資料表的執行效能的邏輯是一樣的。

  此外，在檢視表中建立索引後，SQL Server會在記憶體中儲存檢視表傳回的結果集，當日後查詢時就不必再將檢視表具體化。所謂 具體化 （materializing）指的是每一次當執行查詢需要引用檢視表時，SQL Server就需要動態合併一個檢視表結果集所需資料的處理程序。（請記住檢視表是一個動態的結構）。這個將檢視表具體化的過程其實是會造成系統負擔的。尤其對一個複雜的檢視表或含有大量資料的檢視表而言，必須重複的將檢視表具體化更是影響執行效能。

  對檢視表建立索引的另一個好處是，即使查詢的FROM子 句中未直接使用檢視表的名稱，但SQL Server查詢最佳化器（Query Optimizer）會開始在查詢中使用檢視表索引。換言之，不必重新改寫現有的查詢，就可以從索引的檢視表中擷取資料以提升效能。

  當然，使用索引檢視表改善系統執行效能也不是只有好處沒有壞處，如索引檢視表的維護對SQL Server來說就較為複雜。每一次修改檢視表的底層資料表時，SQL Server就必須更新檢視表結果集和檢視表中的索引。由於一個檢視表的索引範圍可能比單一資料表上的索引還要複雜（例如當檢視表包含數個大型資料表的資料時），使用索引檢視表查詢的優勢可能遠比花在維護檢視表和索引還來的少！基於維護方面的考量，使用索引檢視表前必須考量這樣的設定是否真的達到我們想要改善執行效能的目的。通常，當底層資料表的資料為靜態資料時、當查詢的結果集會運用到大量資料列，或是大部分的查詢都會引用到底層資料表時，我們才考慮使用索引檢視表。

  本章總結
  　

  本章中學習到利用檢視表建立虛擬資料表，檢視表為輔助型的資料結構，雖然外觀和資料表相同，但檢視表儲存的其實是SQL查詢。這些查詢和其他查詢聯結，存取底層資料表的資料。

  在T-SQL陳述式中引用檢視表的方式和引用資料表相同。檢視表也能用於設定安全性層級，保護機密的資料，提供更簡單的方法存取資料。另外，檢視表讓資料的呈現具邏輯性，也可將分割的資料表以檢視表的方式建立單一的虛擬資料表。

  本章中，我們也學習到使用檢視表的限制和原則，並適時的使用索引檢視表。在 19章 中，我們將學習交易和交易鎖定。