SQL資料庫開發—1TSQL—第八章視圖

8.1 視圖概述

視圖是一個虛拟表，其内容由查詢定義。 同表一樣，視圖包含一系列帶有名稱的列和行資料。 視圖在資料庫中并不是以資料值存儲集形式存在，除非是索引視圖。 行和列資料來自由定義視圖的查詢所引用的表，并且在引用視圖時動态生成。

注意在預設情況下，視圖傳回資料要慢。視圖并沒有真正改變内容。視圖存在隻有安全或者簡化。

8.2 視圖分類

标準視圖：将來自一張表或者多張表的資料整理計算，最後合并成一張虛拟表。标準視圖存儲在資料庫中的是查詢語句而不是實際的資料。

索引視圖：是被具體化了的視圖。 這意味着已經對視圖定義進行了計算并且生成的資料像表一樣存儲。 可以為視圖建立索引，即對視圖建立一個唯一的聚集索引。 索引視圖可以顯著提高某些類型查詢的性能。 索引視圖尤其适于聚合許多行的查詢。 但它們不太适于經常更新的基本資料集。因為基表修改時同時需要修改索引視圖。

分區視圖:在一台或多台伺服器間水準連接配接一組成員表中的分區資料。 這樣，資料看上去如同來自于一個表。 聯接同一個 SQL Server 執行個體中的成員表的視圖是一個本地分區視圖。我的了解就是使用UNION将多個查詢結果在一起。

系統視圖：公開目錄中繼資料。 您可以使用系統視圖傳回與 SQL Server 執行個體或在該執行個體中定義的對象有關的資訊。 例如，你可以查詢 sys.databases 目錄視圖以便傳回與執行個體中提供的使用者定義資料庫有關的資訊。

8.3 使用标準視圖

  CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]  

[ WITH <view_attribute> [ ,...n ] ]  

AS select_statement  

[ WITH CHECK OPTION ]  

[ ; ] 

<view_attribute> ::=  

{ 

    [ ENCRYPTION ]      ##加密

    [ SCHEMABINDING ] 

    [ VIEW_METADATA ]      

}  

CHECK OPTION:要求對該視圖執行的所有資料修改語句都必須符合 select_statement 中所設定的條件。 通過視圖修改行時，WITH CHECK OPTION 可確定送出修改後，仍可通過視圖看到資料。

VIEW_METADATA:指定為引用視圖的查詢請求浏覽模式的中繼資料時， SQL Server 執行個體将向 DB-Library、ODBC 和 OLE DB API 傳回有關視圖的中繼資料資訊，而不傳回基表的中繼資料資訊。 浏覽模式中繼資料是 SQL Server 執行個體向這些用戶端 API 傳回的附加中繼資料。

8.3.1 建立視圖

create view send_state

as

SELECT p.TABLE_ID,p.version,a93.increment,a93.CREATION_TIME 入庫時間,min(p.sdatetime) 下發開始時間, max(p.sdatetime) 下發結束時間,datediff(minute,min(p.sdatetime), max(p.sdatetime)) as 下發用時,count(*) as 下發數

 ,(select count(*) from A37_Lane_Info where lane_type<3) as 收費節點數 FROM A93_VERSION_STATE_ZH as a93 join PARAM_SEND_LANE as p on p.table_id=a93.table_id and a93.version_id=p.version and  a93.state=5 and a93.table_id>150000

                                                 join A36_ORGAN as a36 on p.plaza_id=a36.verifysuper_id and a36.organ_no=p.LANE_no and a36.organ_character=13

                                                 join A37_Lane_Info as a37 on a37.lane_id=a36.organ_id and a37.lane_type<3

                                                 group by p.TABLE_ID,p.version,a93.CREATION_TIME,a93.increment ;

select * from send_state;

也可以使用條件

select * from send_state where table_id=151101;

8.3.2 通過視圖更改資料

 視圖不僅可以作為select結果，也可以修改基表資料。

 有以下限制

  1 任何修改（包括 UPDATE、INSERT 和 DELETE 語句）都隻能引用一個基表的列。

  2 無法修改聚合函數：AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 的計算列

  3 無法修改受 GROUP BY、HAVING 或 DISTINCT 子句的影響。

  4 無法修改經過表達式計算的列

  5 無法修改集合運算（union，等等）

create view a70

as

SELECT * FROM A70_USERS_ZH where user_num<15002000

update  A70  set user_name='學習'

基表也受影響了。

8.4 索引視圖

 在視圖上建立索引。 對視圖建立的第一個索引必須是唯一聚集索引。 建立唯一聚集索引後，可以建立更多非聚集索引。 為視圖建立唯一聚集索引可以提高查詢性能，因為視圖在資料庫中的存儲方式與具有聚集索引的表的存儲方式相同。 查詢優化器可使用索引視圖加快執行查詢的速度。 要使優化器考慮将該視圖作為替換，并不需要在查詢中引用該視圖。

 建立索引視圖必須要加上SCHEMABINDING，級聯表。

8.4.1 建立索引視圖所需的 SET 選項

SET 選項	必需的值	預設伺服器值
ANSI_NULLS	ON	ON
ANSI_PADDING	ON	ON
ANSI_WARNINGS1	ON	ON
ARITHABORT	ON	ON
CONCAT_NULL_YIELDS_NULL	ON	ON
NUMERIC_ROUNDABORT	OFF	OFF
QUOTED_IDENTIFIER	ON	ON

8.4.2 建立索引視圖的限制

 1 視圖必須使用SCHEMABINDING選項

 2 視圖不能引用其他的視圖—隻能引用表和UDF（使用者自定義函數）

 3 視圖不能包括任何OUTER  joins

 4 視圖和視圖引用的對象必須在相同的資料庫裡面

8.4.3 建立索引視圖

建立視圖

create view a70

with schemabinding

as

SELECT user_num,user_pwd,user_name,attach_id FROM [dbo].[A70_USERS_ZH];

建立索引

CREATE UNIQUE CLUSTERED INDEX [view-index-a70] ON [dbo].[a70]

(

         [user_num] DESC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

8.5 視圖選項

8.5.1  ENCRYPTION 加密選項

    CREATE VIEW 語句文本的項進行加密。

 8.5.2  SCHEMABINDING相當于級聯表

    将視圖綁定到基礎表的架構。 如果指定了 SCHEMABINDING，則不能按照将影響視圖定義的方式修改基表或表。 必須首先修改或删除視圖定義本身，才能删除将要修改的表的依賴關系。 使用 SCHEMABINDING 時，select_statement 必須包含所引用的表、視圖或使用者定義函數的兩部分名稱 (schema.object)。 所有被引用對象都必須在同一個資料庫内。

不能删除參與了使用 SCHEMABINDING 子句建立的視圖的視圖或表，除非該視圖已被删除或更改而不再具有架構綁定。 否則， 資料庫引擎将引發錯誤。 另外，如果對參與具有架構綁定的視圖的表執行 ALTER TABLE 語句，而這些語句又會影響視圖定義，則這些語句将會失敗。

8.5.3 WITH CHECK OPTION 完整性

進行資料修改，以便僅适用于where條件的。以後對該視圖插入、修改、删除操作時，會自動加上字段where條件的。

1.對于update,有with check option，要保證update後，資料要被視圖查詢出來create view a70

with schemabinding

as

SELECT user_num,user_pwd,user_name,attach_id FROM [dbo].[A70_USERS_ZH];

2.對于delete,有無with check option都一樣

4.對于insert,有with check option，要保證insert後，資料要被視圖查詢出來

5 對于沒有where 子句的視圖，使用with check option是多餘的。

create view a70

with schemabinding

as

SELECT user_num,user_pwd,user_name,attach_id FROM [dbo].[A70_USERS_ZH];

 總結ENCRYPTION選項是對建立視圖的語句加密，SCHEMABINDING 的作用是在表結構層面的，WITH CHECK OPTION選項的作用是在資料層的。