檢視(VIEW) 同時也稱為虛擬資料表,它將一連串複雜的 SELECT 陳述式組合成一個虛擬資料表,可以用來簡化日後的查詢操作。 除了這個優點,你也可以透過 VIEW 來更新資料。此外,若搭配使用索引檢視(Indexed View)還可以用來提升查詢效能。
Creating a View
CREATE VIEW
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ]
View 的禁止事項
在 VIEW 中的 SELECT 陳述式可以參考 Table, View, Function ,但是不允許以下項目:
- 無法使用 COMPUTE 或 COMPUTE BY 子句
- 無法使用 SELECT/INTO 關鍵字
- 無法使用 OPTION 子句
- 無法參考 temporary table 或 table variable
- 使用 ORDER BY 子句,除非 SELECT 陳述式的選取清單中也有 TOP 子句
- 無法叫用 Stored Procedure
- 無法接受參數
參數說明
SCHEMABINDING
在 VIEW 中參考到的實際資料表稱之為基底資料表,當指定 SCHEMABINDING 選項時,若異動基底資料表的 shcema 會引響到這個 VIEW 的話,則這個 VIEW 將會限制基底資料表的 shcema 修改。
例如以下這個範例,因為使用 SCHEMABINDING ,所以你無法任意的變更 tblStudent 的結構,例如將 StudentNo 變更成 StudentID。 若要變更,必須先修改或卸除資料表的相依性。
CREATE VIEW vw_GradeTable WITH SCHEMABINDING AS SELECT S.StudentNo, S.StudentName, C.ClassName, G.Grade FROM dbo.tblStudent S INNER JOIN dbo.tblGrade G ON S.StudentNo = G.StudentNo INNER JOIN dbo.tblClass C ON C.ClassID=G.ClassID
注意
當使用 SCHEMABINDING 選項時,一定要使用 two-part name 來指定資料表名稱。也就是 ( schemaName.tableName) eg: dbo.tblStudent
CHECK OPTION
強制執行所有針對檢視來執行的資料修改陳述式遵照 select_statement 內所設定的準則。 當利用檢視來修改資料列時,WITH CHECK OPTION 可確保在認可修改之後,仍可以透過檢視見到資料。
CREATE VIEW dbo.vw_Product WITH SCHEMABINDING AS SELECT ProductID,ProductName,Price FROM dbo.Products WHERE Price<7 WITH CHECK OPTION
上面這個 VIEW 使用 WITH CHECK OPTION ,用來限制新增或修改資料後,該資料都還必須在 VIEW 之中;刪除當然只能刪除 VIEW 之中的資料。
目前view中的資料
select * from vw_Product
執行更新,價格不能超出 WHERE 條件
update vw_Product set Price=6 where ProductID=20 update vw_Product set Price=60 where ProductID=20
執行新增,價格也不能超出 WHERE 條件
insert vw_Product(ProductName, Price) Values ('aaa',5) insert vw_Product(ProductName, Price) Values ('bbb',20)
如何避免修改基底資料表的結構後,造成檢視(View)無法使用
在 VIEW 中所使用到的實際資料表稱之為基底資料表(base table),SQL Server 並不會真正儲存檢視中的資料,而是儲存這些基底資料表的 metadata ,當您修改基底資料表的結構時,檢視的 metadata 並不會跟著更新,因而造成例外發生。 該如何避免這個問題,可參考以下文章。
http://www.dotblogs.com.tw/terrychuang/archive/2012/04/17/71581.aspxModifying Data Through a View
刪除
如果檢視參考多個基底資料表,就不能直接透過檢視表刪除資料列。您只可以更新屬於單一基底資料表的資料行。
新增
如果檢視參考多個基底資料表,就不能直接透過檢視表插入資料列。
修改
支援更新的檢視表稱為"可更新檢視表",要透過檢視直接修改資料,該檢視必須符合以下限制:
- 要更新位於 VIEW 中的資料行,可以明確對應到單一基底資料表中的資料行
- 不能更新 aggregation 資料行
- 不能含 UNION / UNION ALL / CROSSJOIN / EXCEPT / INTERSECT 子句。
- 不能含 DISTINCT, GROUP BY, HAVING 子句。
- 不能含 TOP 子句。
分割檢視(Partitioned Views)
參考 MSDN:使用資料分割檢視
Creating an Indexed View
決定性(Determinism)
什麼是決定性(Determinism)
一個函式若是決定性函式,表示這個函式,無論何時叫用,只要給定相同輸入值,就會回傳相同的結果。 例如 SUBSTRING 、 YEAR 、 ROUND ,這些都是決定性函式。 而像 RAND 、 GETDATE 、 NEWID ,這些都是非決定性函式。
在設計資料庫時,這個特性有時候是必須的。 例如:若我們要使用一個計算欄位(computed column)來建立索引,則這個欄位就不能由非決定性函式構成。
參考 MSDN:決定性與非決定性函數
沒有留言:
張貼留言