1，什么是視圖？

2，為什么要用視圖；

3，視圖中的ORDER BY;

4，刷新視圖；

5，更新視圖；

6，視圖選項(xiàng)；

7，索引視圖；

1.什么是視圖

　　視圖是由一個(gè)查詢(xún)所定義的虛擬表，它與物理表不同的是，視圖中的數(shù)據(jù)沒(méi)有物理表現(xiàn)形式，除非你為其創(chuàng)建一個(gè)索引；如果查詢(xún)一個(gè)沒(méi)有索引的視圖，Sql Server實(shí)際訪問(wèn)的是基礎(chǔ)表。

　　如果你要?jiǎng)?chuàng)建一個(gè)視圖，為其指定一個(gè)名稱(chēng)和查詢(xún)即可。Sql Server只保存視圖的元數(shù)據(jù)，用戶(hù)描述這個(gè)對(duì)象，以及它所包含的列，安全，依賴(lài)等。當(dāng)你查詢(xún)視圖時(shí)，無(wú)論是獲取數(shù)據(jù)還是更新數(shù)據(jù)，Sql server都用視圖的定義來(lái)訪問(wèn)基礎(chǔ)表；

　　視圖在我們?nèi)粘２僮饕舶缪葜S多重要的角色，比如可以利用視圖訪問(wèn)經(jīng)過(guò)篩選和處理的數(shù)據(jù)，而不是直接訪問(wèn)基礎(chǔ)表，以及在一定程度上也保護(hù)了基礎(chǔ)表。

我們?cè)趧?chuàng)建視圖的時(shí)候，也要遵守三個(gè)規(guī)則：

不能在視圖定義中指定ORDER BY ，除非定義中包含Top或For Xml 說(shuō)明；
所有的列必須有列名；
這些所有的列名必須唯一；

　　對(duì)于視圖表中在沒(méi)有top或for xml說(shuō)明的情況下，不能有Order by 語(yǔ)句，這是因?yàn)橐晥D被認(rèn)為是一個(gè)表，表是一個(gè)邏輯的實(shí)體，它的行是沒(méi)有順序的。視圖中所有列必須有列名，且唯一的情況我想大家都理解；

下面的sql語(yǔ)句表示創(chuàng)建一個(gè)簡(jiǎn)單的視圖：

2.為什么要使用視圖（更新）

　　SqlServer既然給我們提供這樣的對(duì)象，就一定有它的作用。而我們?cè)谑褂靡晥D上，要么用的過(guò)多，要么用的不夠，所以一部分人建議不要用視圖，而一部分人又建議少用。那我們聽(tīng)誰(shuí)的呢？

　　其實(shí)我們要是掌握了用視圖的目的，就能在正確的地方，用正確的視圖；那么視圖能給我們解決什么問(wèn)題呢？

1)，為最終用戶(hù)減少數(shù)據(jù)庫(kù)呈現(xiàn)的復(fù)雜性?？蛻?hù)端只要對(duì)視圖寫(xiě)簡(jiǎn)單的代碼，就能返回我所需要的數(shù)據(jù)，一些復(fù)雜的邏輯操作，放在了視圖中來(lái)完成；

2)，防止敏感的列被選中，同時(shí)仍然提供對(duì)其他重要數(shù)據(jù)的訪問(wèn)；

3)，對(duì)視圖添加一些額外的索引，來(lái)提高查詢(xún)的效率；

　　視圖其實(shí)沒(méi)有改變?nèi)魏问虑?，只是?duì)訪問(wèn)的數(shù)據(jù)進(jìn)行了某種形式的篩選?？紤]一下視圖的作用，你應(yīng)該能看到視圖的概念如何為缺乏經(jīng)驗(yàn)的用戶(hù)簡(jiǎn)化數(shù)據(jù)(只顯示他們關(guān)心的數(shù)據(jù))，或者不給予用戶(hù)訪問(wèn)基礎(chǔ)表的
權(quán)利，但授予他們?cè)L問(wèn)不包含敏感數(shù)據(jù)視圖的權(quán)力，從而提前隱藏敏感數(shù)據(jù)。

　　要知道，在默認(rèn)的情況下，視圖沒(méi)有做什么特殊的事情。視圖就好象一個(gè)查詢(xún)那樣從命令行運(yùn)行(這里不存在任何形式的預(yù)先優(yōu)化),這意味著在數(shù)據(jù)請(qǐng)求和將被交付的數(shù)據(jù)之間多加了一層開(kāi)銷(xiāo)。這表明視圖絕不可能像
只是直接運(yùn)行底層SELECT語(yǔ)句那樣快。不過(guò)，視圖存在有一個(gè)原因--這就是它的安全性或?yàn)橛脩?hù)所做的簡(jiǎn)化，在你的需要和開(kāi)銷(xiāo)之間權(quán)衡，找到最適合特定情況的解決方案。

3.視圖中的ORDER BY

視圖表示一個(gè)邏輯實(shí)體，它與表非常類(lèi)似；

如果我們?cè)谏厦娴膭?chuàng)建的sql語(yǔ)句中加一個(gè)Order BY 語(yǔ)句，看看有什么效果：

運(yùn)行該語(yǔ)句將會(huì)失敗，回收到以下的提示：

Msg 1033, Level 15, State 1, Procedure V1, Line 5
除非另外還指定了 TOP 或 FOR XML，否則，ORDER BY 子句在視圖、內(nèi)聯(lián)函數(shù)、派生表、子查詢(xún)和公用表表達(dá)式中無(wú)效。

根據(jù)提示，ORDER By 也不是不能用，只有指定了Top或for xml語(yǔ)句后，ORDER BY 才能使用,如：

但是，并不建議在視圖中使用ORDER BY ,這是因?yàn)橐晥D表示一個(gè)表，而對(duì)于表來(lái)說(shuō)，是不會(huì)有排序的；所以建議在查詢(xún)視圖的時(shí)候，用ORDER BY;

SQL Server2005聯(lián)機(jī)叢書(shū)有一段這樣的描述：“在視圖、內(nèi)聯(lián)函數(shù)、派生表或子查詢(xún)的定義中使用ORDER BY 字句，子句只能用戶(hù)確定TOP子句返回的行。ORDER BY 不保證在查詢(xún)這些構(gòu)造時(shí)得到有序結(jié)果，除非在查詢(xún)本省也指定了ORDER BY.”

4.刷新視圖

我在上面說(shuō)過(guò)，視圖會(huì)保存元數(shù)據(jù)，列，安全，以及依賴(lài)等信息，如果我們把基礎(chǔ)表的架構(gòu)更改了，并不會(huì)直接反映到視圖上來(lái)；更改架構(gòu)后，使用sp_refreshview存儲(chǔ)過(guò)程刷新視圖的元數(shù)據(jù)是一個(gè)好習(xí)慣；

比如我們創(chuàng)建了一個(gè)表T1和一個(gè)T1的視圖V1,然后更改T1,再看V1的結(jié)果：

首先創(chuàng)建表T1:

然后創(chuàng)建T1的視圖V1:

在現(xiàn)實(shí)實(shí)踐中，要避免在視圖中的SELECT語(yǔ)句中使用*,在這只是演示。如果你查詢(xún)視圖V1就會(huì)出現(xiàn)以下結(jié)果：

接下來(lái)，我們對(duì)表T1添加一列col3:

然后再次查詢(xún)視圖V1，你想這時(shí)的結(jié)果是三列呢，還是而列呢？答案是二列。T1架構(gòu)的改變，并沒(méi)有影響到視圖的元數(shù)據(jù)中，這時(shí)候，如果我們要刷新一下視圖V1,我們就可以用:EXEC sp_refreshview V1 命令， 再次查詢(xún)，V1的結(jié)果就是三列了。

5.更新視圖

　　視圖是一個(gè)虛擬表，我們?cè)诓樵?xún)視圖的時(shí)候，實(shí)際上是對(duì)基礎(chǔ)表的查詢(xún)。視圖不僅可以作為SELECT查詢(xún)的目標(biāo)，也可以作為修改語(yǔ)句的目標(biāo)。當(dāng)然，當(dāng)你修改視圖的時(shí)候，修改的時(shí)候是對(duì)基礎(chǔ)表的修改，它就好像是一個(gè)代理。當(dāng)然，如果不允許直接修改基礎(chǔ)表，只允許修改視圖，就可以限制你要公開(kāi)的數(shù)據(jù)。這樣，就可以對(duì)你的數(shù)據(jù)起到一定的保護(hù)作用,不過(guò)這種限制的時(shí)候很少。

那么在更新視圖的時(shí)候，有哪些限制條件呢？

1)，只要視圖有一列不能隱式獲取值，你就不能想視圖中插入數(shù)據(jù),如果列允許NULL、有默認(rèn)值或者IDETITY屬性，則說(shuō)明它可以隱式獲取值；

2)，如果視圖包含聯(lián)結(jié)，UPDATE或INSERT語(yǔ)句只能影響聯(lián)結(jié)的一端。也就是說(shuō)，INSERT或UPDATE語(yǔ)句必須定義目標(biāo)列列表，這些列只能數(shù)據(jù)聯(lián)結(jié)的一端。你不能從由聯(lián)結(jié)查詢(xún)定義的視圖中刪除數(shù)據(jù)；

3)，不能修改作為計(jì)算結(jié)果的列。如：標(biāo)量表達(dá)式和聚合函數(shù)，SqlServer不會(huì)嘗試改變數(shù)據(jù)庫(kù)引擎的計(jì)算結(jié)果；

4)，如果在創(chuàng)建或修改視圖時(shí)指定了WITH CHECK OPTION選項(xiàng)，與視圖的查詢(xún)篩選器有沖突的INSERT或UPDATE語(yǔ)句將被拒絕；我在“視圖選項(xiàng)”一節(jié)詳細(xì)講解一下。

如果視圖上定義了INSERT OF觸發(fā)器，則違反這些限制的數(shù)據(jù)修改語(yǔ)句可以被執(zhí)行。在INSERT OF觸發(fā)器中你可以用自己的代碼替換原始修改；

當(dāng)你允許對(duì)有聯(lián)結(jié)查詢(xún)定義的視圖執(zhí)行修改的時(shí)候，一定要謹(jǐn)慎，比如一對(duì)多的關(guān)系，如果你根據(jù)“多”的某一索引值修改對(duì)應(yīng)“一”端某列值的記錄，那么結(jié)果就可想而知；

6.視圖選項(xiàng)

當(dāng)你創(chuàng)建或修改視圖時(shí)，可以指定一些選項(xiàng)，這些選項(xiàng)用戶(hù)控制視圖的行為和功能。

ENCRYPTION、SCHEMABINDING和VIEW_METADATA選項(xiàng)在視圖頭指定，CHECK OPTION選項(xiàng)則在查詢(xún)之后指定；

如：       

1),ENCRYPTION

如果你在構(gòu)建任何類(lèi)型的商業(yè)軟件的時(shí)候，需要對(duì)視圖進(jìn)行加密的時(shí)候，這是一個(gè)不錯(cuò)的選項(xiàng)。

如果未指定ENCRYPTION選項(xiàng)，SQLSERVEr則以純文本的形式保存用戶(hù)定義的語(yǔ)句，如果指定了ENCRYPTION選項(xiàng)，對(duì)象的文本則會(huì)被混淆。

SQLSERVER提供了一個(gè)系統(tǒng)函數(shù)sp_helptext查看視圖的文本，如果應(yīng)用的ENCRYPTION選項(xiàng)，則會(huì)得到“The text for object ‘xx' is encrypted”語(yǔ)句；

注：在加密之前一定要先備份你所要加密的視圖，一旦加密，就不能回頭。

2),SCHEMABINDING

如果你使用SCHEMABINDING選項(xiàng)創(chuàng)建視圖，SQLSERVER將不允許刪除基礎(chǔ)表或修改被引用的列，防止在對(duì)底層對(duì)象修改時(shí)，使視圖變得“孤立”，如果某人沒(méi)有注意到你的視圖，執(zhí)行了DROP,刪除視圖引用的列或其他一些操作，那就很糟糕。如果使用SCHEMABINDING選項(xiàng)，則就可以避免這種情況。

如果想在視圖上創(chuàng)建索引，則必須使用SCHMABINDING選項(xiàng)；

如果應(yīng)用這個(gè)選項(xiàng)，則定義視圖的時(shí)候要注意兩點(diǎn)：

　　1，所有對(duì)象必須由兩部分構(gòu)成的名稱(chēng)，如：應(yīng)該使用dbo.Orders 而不能是Orders

　　2，不能在SELECT列表使用*,所有的列名必須指定一個(gè)名稱(chēng)；

3),CHECK OPTION

使用WITH CHECK OPTION 創(chuàng)建的視圖能防止與視圖查詢(xún)篩選器有沖突的INSERT或UPDATE語(yǔ)句。沒(méi)有該選項(xiàng)，視圖可以接受不符合查詢(xún)篩選器的修改。比如：

我們?cè)贜orthwind數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)CustomWithOrder的視圖，現(xiàn)在還沒(méi)有添加WITH CHECK OPTION選項(xiàng)

該視圖的作用是查詢(xún)所有有訂單的客戶(hù)的id和公司名，接下來(lái)我們向視圖中插入一條不存在的用戶(hù)id,和公司名：

執(zhí)行成功，然后在查詢(xún)這個(gè)CustomerWithOrder視圖，很明顯，查詢(xún)不到CustomerID為'MySQL'的用戶(hù)，因?yàn)橐晥D只包含發(fā)生過(guò)訂單的用戶(hù)；如果你直接查詢(xún)Customers表，就會(huì)發(fā)現(xiàn)這個(gè)新增的用戶(hù)信息了。

接下來(lái)對(duì)CustomerWithOrder視圖添加WITH CHECK OPTION 選項(xiàng)

然后再執(zhí)行下面的語(yǔ)句：

你會(huì)收到以下錯(cuò)誤：

Msg 550, Level 16, State 1, Line 2
試圖進(jìn)行的插入或更新已失敗，原因是目標(biāo)視圖或者目標(biāo)視圖所跨越的某一視圖指定了 WITH CHECK OPTION，而該操作的一個(gè)或多個(gè)結(jié)果行又不符合 CHECK OPTION 約束。
語(yǔ)句已終止。 

4),VIEW_METADATA

　　該選項(xiàng)的作用是，讓視圖看起來(lái)更像一個(gè)真正的表。不使用該選項(xiàng)，返回給客戶(hù)端的api的元數(shù)據(jù)將是視圖所依賴(lài)的基礎(chǔ)表的數(shù)據(jù)；

　　如果客戶(hù)端希望SqlServer發(fā)送視圖的元數(shù)據(jù)信息，而不是基礎(chǔ)表的元數(shù)據(jù)時(shí)，可以在創(chuàng)建或修改視圖時(shí)指定此選項(xiàng)；是不是聽(tīng)的很費(fèi)勁，聽(tīng)我慢慢說(shuō)；

　　假設(shè)用戶(hù)擁有對(duì)視圖的操作權(quán)限，而沒(méi)有對(duì)基礎(chǔ)表操作的權(quán)限，那么用戶(hù)對(duì)視圖執(zhí)行一些操作，如果指定了VIEW_METADATA選項(xiàng)，那么該語(yǔ)句將會(huì)違背安全而失敗，因?yàn)橹灰付薞IEW_METADATA那么返回給客戶(hù)端就是視圖的元數(shù)據(jù)，而不是基礎(chǔ)表的元數(shù)據(jù)。另一方面，如果用戶(hù)嘗試通過(guò)視圖修改數(shù)據(jù)，而該操作又與視圖上定義的CHECK OPTION有沖突，這種操作只有直接提交到基礎(chǔ)表，才有可能成功。

　　SqlServer中就有這樣的工具，在SqlServer2000中，企業(yè)管理器就是，如果我們向視圖中插入一條記錄，比如向在有WITH CHECK OPTION選項(xiàng)的CustomerWithOrder視圖中插入一個(gè)任意的消費(fèi)者無(wú)論存在與否，并打開(kāi)跟蹤企業(yè)管理器提交到Sql Server中的操作，你會(huì)發(fā)現(xiàn)操作實(shí)際把基礎(chǔ)表作為目標(biāo)提交的，及時(shí)他違背CHECK OPTION，也會(huì)成功。而在Sql Server2005中的SSMS中，就會(huì)不同了，如果在“Modify”視圖中，手動(dòng)插入一條記錄，就可以成功，說(shuō)明雖然指定了VIEW_METADATA和CHECK OPTION選項(xiàng)，它還是插入到了基礎(chǔ)表中了，可以跟蹤一下提交到Sqlserver的操作(用Sql server Profiler)。但如果在由“Open View”產(chǎn)生的面板中進(jìn)行操作，將會(huì)失敗，提示：

　　可以再次跟蹤提交到Sql server的操作，就能看到，他提交到目標(biāo)對(duì)象是視圖；

　　還是那句話(huà)：如果客戶(hù)端希望SqlServer發(fā)送視圖的元數(shù)據(jù)信息，而不是基礎(chǔ)表的元數(shù)據(jù)時(shí)，可以在創(chuàng)建或修改視圖時(shí)指定此選項(xiàng)

　　這次明白了嗎？

　　我個(gè)人總結(jié)，只要有VIEW_METADATA選項(xiàng)就有必要加上CHECK OPTION選項(xiàng)，而SCHEMABINDING選項(xiàng)，最好也要加上，防止你的視圖“孤立”，而在索引視圖中SCHEMABINDING選項(xiàng)是必須加上的。

7.索引視圖

　　如果沒(méi)有索引，視圖中的數(shù)據(jù)不會(huì)有任何物理表現(xiàn)形似，如果加上索引，則就把視圖中的數(shù)據(jù)物理化了，SqlServer會(huì)在修改基礎(chǔ)表時(shí)同步索引視圖。但你不能直接同步視圖內(nèi)容。

　　我們知道在表上創(chuàng)建索引，能提高性能，相同，在視圖也是一樣，在視圖上創(chuàng)建的第一個(gè)索引必須是唯一聚集索引，之后才可以創(chuàng)建其他的非聚集索引。

　　索引視圖必須使用SCHEMABINDING選項(xiàng)，并且不能引用其他視圖，只能引用基礎(chǔ)表和UDF,而基礎(chǔ)表和UDF必須使用兩部分命名約定來(lái)引用(參見(jiàn)5.視圖選項(xiàng)中的SCHEMABINDING選項(xiàng))。

　　除了性能，你可能還會(huì)因?yàn)槠渌蚴褂盟饕晥D，比如在一張基礎(chǔ)表中有一列我們要強(qiáng)制該列中已知值的唯一性，但是允許出現(xiàn)多次的NULL值，我們?cè)趺崔k呢，我們首先想到的可能是用UNIQUE約束，但是UNIQUE會(huì)認(rèn)為兩個(gè)NULL值相等，那么這個(gè)不得不放棄了，那還有什么辦法呢？

　　其實(shí)我們可以利用一個(gè)索引視圖來(lái)完成這個(gè)任務(wù)，利用索引視圖篩選所有非NULL的數(shù)據(jù)，那么這種索引將防止重復(fù)的已知值進(jìn)入基礎(chǔ)表，但允許多個(gè)NULL，因?yàn)镹ULL不是唯一索引的一部分，我們?cè)谙蚧A(chǔ)表中插入數(shù)據(jù)的時(shí)候，就利用索引視圖的UNIQUE來(lái)限制我們的數(shù)據(jù)，來(lái)達(dá)到某列中強(qiáng)制已知值的唯一性的目的；

我們可以演示一下，首先創(chuàng)建一個(gè)基礎(chǔ)表T2和一個(gè)索引視圖V2:

然后我們向T2表中插入以下數(shù)據(jù)：

那么以上4條INSERT哪條會(huì)失敗呢？答案是2。最后讓我們SELECT 一下基礎(chǔ)表T2，看實(shí)現(xiàn)我們開(kāi)始那個(gè)要求了嗎？

執(zhí)行：

最新評(píng)論