细说SQL Server中的视图
时间：2020-11-22 22:47:37 | 栏目：.NET代码 | 点击：次
1，什么是视图？
2，为什么要用视图；
3，视图中的ORDER BY;
4，刷新视图；
5，更新视图；
6，视图选项；
7，索引视图；
1.什么是视图
视图是由一个查询所定义的虚拟表，它与物理表不同的是，视图中的数据没有物理表现形式，除非你为其创建一个索引；如果查询一个没有索引的视图，Sql Server实际访问的是基础表。
如果你要创建一个视图，为其指定一个名称和查询即可。Sql Server只保存视图的元数据，用户描述这个对象，以及它所包含的列，安全，依赖等。当你查询视图时，无论是获取数据还是更新数据，Sql server都用视图的定义来访问基础表；
视图在我们日常操作也扮演着许多重要的角色，比如可以利用视图访问经过筛选和处理的数据，而不是直接访问基础表，以及在一定程度上也保护了基础表。
我们在创建视图的时候，也要遵守三个规则：
不能在视图定义中指定ORDER BY ，除非定义中包含Top或For Xml 说明；
所有的列必须有列名；
这些所有的列名必须唯一；
对于视图表中在没有top或for xml说明的情况下，不能有Order by 语句，这是因为视图被认为是一个表，表是一个逻辑的实体，它的行是没有顺序的。视图中所有列必须有列名，且唯一的情况我想大家都理解；
下面的sql语句表示创建一个简单的视图：
复制代码 代码如下:
CREATE VIEW dbo.V1
AS
SELECT CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
AS
SELECT CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
2.为什么要使用视图（更新）
SqlServer既然给我们提供这样的对象，就一定有它的作用。而我们在使用视图上，要么用的过多，要么用的不够，所以一部分人建议不要用视图，而一部分人又建议少用。那我们听谁的呢？
其实我们要是掌握了用视图的目的，就能在正确的地方，用正确的视图；那么视图能给我们解决什么问题呢？
1)，为最终用户减少数据库呈现的复杂性。客户端只要对视图写简单的代码，就能返回我所需要的数据，一些复杂的逻辑操作，放在了视图中来完成；
2)，防止敏感的列被选中，同时仍然提供对其他重要数据的访问；
3)，对视图添加一些额外的索引，来提高查询的效率；
视图其实没有改变任何事情，只是对访问的数据进行了某种形式的筛选。考虑一下视图的作用，你应该能看到视图的概念如何为缺乏经验的用户简化数据(只显示他们关心的数据)，或者不给予用户访问基础表的
权利，但授予他们访问不包含敏感数据视图的权力，从而提前隐藏敏感数据。
要知道，在默认的情况下，视图没有做什么特殊的事情。视图就好象一个查询那样从命令行运行(这里不存在任何形式的预先优化),这意味着在数据请求和将被交付的数据之间多加了一层开销。这表明视图绝不可能像
只是直接运行底层SELECT语句那样快。不过，视图存在有一个原因--这就是它的安全性或为用户所做的简化，在你的需要和开销之间权衡，找到最适合特定情况的解决方案。
3.视图中的ORDER BY
视图表示一个逻辑实体，它与表非常类似；
如果我们在上面的创建的sql语句中加一个Order BY 语句，看看有什么效果：
复制代码 代码如下:
ALTER VIEW dbo.V1
AS
SELECT CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
ORDER BY CompanyName
AS
SELECT CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
ORDER BY CompanyName
运行该语句将会失败，回收到以下的提示：
Msg 1033, Level 15, State 1, Procedure V1, Line 5
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
根据提示，ORDER By 也不是不能用，只有指定了Top或for xml语句后，ORDER BY 才能使用,如：
复制代码 代码如下:
ALTER VIEW dbo.V1
AS
SELECT TOP(10) CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
ORDER BY CompanyName
AS
SELECT TOP(10) CustomerID,CompanyName FROM Customers
WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
ORDER BY CompanyName
但是，并不建议在视图中使用ORDER BY ,这是因为视图表示一个表，而对于表来说，是不会有排序的；所以建议在查询视图的时候，用ORDER BY;
SQL Server2005联机丛书有一段这样的描述：“在视图、内联函数、派生表或子查询的定义中使用ORDER BY 字句，子句只能用户确定TOP子句返回的行。ORDER BY 不保证在查询这些构造时得到有序结果，除非在查询本省也指定了ORDER BY.”
4.刷新视图
我在上面说过，视图会保存元数据，列，安全，以及依赖等信息，如果我们把基础表的架构更改了，并不会直接反映到视图上来；更改架构后，使用sp_refreshview存储过程刷新视图的元数据是一个好习惯；
比如我们创建了一个表T1和一个T1的视图V1,然后更改T1,再看V1的结果：
首先创建表T1:
复制代码 代码如下:
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
CREATE TABLE T1(col1 INT,col2 INT)
INSERT INTO T1(col1,col2) VALUES(1,2)
GO
DROP TABLE T1
CREATE TABLE T1(col1 INT,col2 INT)
INSERT INTO T1(col1,col2) VALUES(1,2)
GO
然后创建T1的视图V1:
复制代码 代码如下:
CREATE VIEW V1
AS
SELECT * FROM T1
AS
SELECT * FROM T1
在现实实践中，要避免在视图中的SELECT语句中使用*,在这只是演示。如果你查询视图V1就会出现以下结果：
接下来，我们对表T1添加一列col3:
复制代码 代码如下:
ALTER TABLE T1 ADD col3 INT
然后再次查询视图V1，你想这时的结果是三列呢，还是而列呢？答案是二列。T1架构的改变，并没有影响到视图的元数据中，这时候，如果我们要刷新一下视图V1,我们就可以用:EXEC sp_refreshview V1 命令， 再次查询，V1的结果就是三列了。
5.更新视图
视图是一个虚拟表，我们在查询视图的时候，实际上是对基础表的查询。视图不仅可以作为SELECT查询的目标，也可以作为修改语句的目标。当然，当你修改视图的时候，修改的时候是对基础表的修改，它就好像是一个代理。当然，如果不允许直接修改基础表，只允许修改视图，就可以限制你要公开的数据。这样，就可以对你的数据起到一定的保护作用,不过这种限制的时候很少。
那么在更新视图的时候，有哪些限制条件呢？
1)，只要视图有一列不能隐式获取值，你就不能想视图中插入数据,如果列允许NULL、有默认值或者IDETITY属性，则说明它可以隐式获取值；
2)，如果视图包含联结，UPDATE或INSERT语句只能影响联结的一端。也就是说，INSERT或UPDATE语句必须定义目标列列表，这些列只能数据联结的一端。你不能从由联结查询定义的视图中删除数据；
3)，不能修改作为计算结果的列。如：标量表达式和聚合函数，SqlServer不会尝试改变数据库引擎的计算结果；
4)，如果在创建或修改视图时指定了WITH CHECK OPTION选项，与视图的查询筛选器有冲突的INSERT或UPDATE语句将被拒绝；我在“视图选项”一节详细讲解一下。
如果视图上定义了INSERT OF触发器，则违反这些限制的数据修改语句可以被执行。在INSERT OF触发器中你可以用自己的代码替换原始修改；
当你允许对有联结查询定义的视图执行修改的时候，一定要谨慎，比如一对多的关系，如果你根据“多”的某一索引值修改对应“一”端某列值的记录，那么结果就可想而知；
6.视图选项
当你创建或修改视图时，可以指定一些选项，这些选项用户控制视图的行为和功能。
ENCRYPTION、SCHEMABINDING和VIEW_METADATA选项在视图头指定，CHECK OPTION选项则在查询之后指定；
如：
复制代码 代码如下:
CREATE VIEW v2
WITH ENCRYPTION,SCHEMABINDING,VIEW_METADATA
AS
SELECT OrderID FROM dbo.Orders
WITH CHECK OPTION
WITH ENCRYPTION,SCHEMABINDING,VIEW_METADATA
AS
SELECT OrderID FROM dbo.Orders
WITH CHECK OPTION
1),ENCRYPTION
如果你在构建任何类型的商业软件的时候，需要对视图进行加密的时候，这是一个不错的选项。
如果未指定ENCRYPTION选项，SQLSERVEr则以纯文本的形式保存用户定义的语句，如果指定了ENCRYPTION选项，对象的文本则会被混淆。
SQLSERVER提供了一个系统函数sp_helptext查看视图的文本，如果应用的ENCRYPTION选项，则会得到“The text for object ‘xx' is encrypted”语句；
注：在加密之前一定要先备份你所要加密的视图，一旦加密，就不能回头。
2),SCHEMABINDING
如果你使用SCHEMABINDING选项创建视图，SQLSERVER将不允许删除基础表或修改被引用的列，防止在对底层对象修改时，使视图变得“孤立”，如果某人没有注意到你的视图，执行了DROP,删除视图引用的列或其他一些操作，那就很糟糕。如果使用SCHEMABINDING选项，则就可以避免这种情况。
如果想在视图上创建索引，则必须使用SCHMABINDING选项；
如果应用这个选项，则定义视图的时候要注意两点：
1，所有对象必须由两部分构成的名称，如：应该使用dbo.Orders 而不能是Orders
2，不能在SELECT列表使用*,所有的列名必须指定一个名称；
3),CHECK OPTION
使用WITH CHECK OPTION 创建的视图能防止与视图查询筛选器有冲突的INSERT或UPDATE语句。没有该选项，视图可以接受不符合查询筛选器的修改。比如：
我们在Northwind数据库中创建一个CustomWithOrder的视图，现在还没有添加WITH CHECK OPTION选项
复制代码 代码如下:
CREATE VIEW CustomerWithOrder
WITH VIEW_METADATA
AS
SELECT Customers.CustomerID,Customers.CompanyName FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
WITH VIEW_METADATA
AS
SELECT Customers.CustomerID,Customers.CompanyName FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
该视图的作用是查询所有有订单的客户的id和公司名，接下来我们向视图中插入一条不存在的用户id,和公司名：
复制代码 代码如下:
INSERT INTO CustomerWithOrder(CustomerID,CompanyName) VALUES('MYSQL','MyReed')
执行成功，然后在查询这个CustomerWithOrder视图，很明显，查询不到CustomerID为'MySQL'的用户，因为视图只包含发生过订单的用户；如果你直接查询Customers表，就会发现这个新增的用户信息了。
接下来对CustomerWithOrder视图添加WITH CHECK OPTION 选项
复制代码 代码如下:
ALTER VIEW CustomerWithOrder
WITH VIEW_METADATA
AS
SELECT Customers.CustomerID,Customers.CompanyName FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
WITH CHECK OPTION
WITH VIEW_METADATA
AS
SELECT Customers.CustomerID,Customers.CompanyName FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
WITH CHECK OPTION
然后再执行下面的语句：
复制代码 代码如下:
INSERT INTO CustomerWithOrder(CustomerID,CompanyName) VALUES('ILSQL','MyReed')
你会收到以下错误：
Msg 550, Level 16, State 1, Line 2
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
语句已终止。
4),VIEW_METADATA
该选项的作用是，让视图看起来更像一个真正的表。不使用该选项，返回给客户端的api的元数据将是视图所依赖的基础表的数据；
如果客户端希望SqlServer发送视图的元数据信息，而不是基础表的元数据时，可以在创建或修改视图时指定此选项；是不是听的很费劲，听我慢慢说；
假设用户拥有对视图的操作权限，而没有对基础表操作的权限，那么用户对视图执行一些操作，如果指定了VIEW_METADATA选项，那么该语句将会违背安全而失败，因为只要指定了VIEW_METADATA那么返回给客户端就是视图的元数据，而不是基础表的元数据。另一方面，如果用户尝试通过视图修改数据，而该操作又与视图上定义的CHECK OPTION有冲突，这种操作只有直接提交到基础表，才有可能成功。
SqlServer中就有这样的工具，在SqlServer2000中，企业管理器就是，如果我们向视图中插入一条记录，比如向在有WITH CHECK OPTION选项的CustomerWithOrder视图中插入一个任意的消费者无论存在与否，并打开跟踪企业管理器提交到Sql Server中的操作，你会发现操作实际把基础表作为目标提交的，及时他违背CHECK OPTION，也会成功。而在Sql Server2005中的SSMS中，就会不同了，如果在“Modify”视图中，手动插入一条记录，就可以成功，说明虽然指定了VIEW_METADATA和CHECK OPTION选项，它还是插入到了基础表中了，可以跟踪一下提交到Sqlserver的操作(用Sql server Profiler)。但如果在由“Open View”产生的面板中进行操作，将会失败，提示：
可以再次跟踪提交到Sql server的操作，就能看到，他提交到目标对象是视图；
还是那句话：如果客户端希望SqlServer发送视图的元数据信息，而不是基础表的元数据时，可以在创建或修改视图时指定此选项
这次明白了吗？
我个人总结，只要有VIEW_METADATA选项就有必要加上CHECK OPTION选项，而SCHEMABINDING选项，最好也要加上，防止你的视图“孤立”，而在索引视图中SCHEMABINDING选项是必须加上的。
7.索引视图
如果没有索引，视图中的数据不会有任何物理表现形似，如果加上索引，则就把视图中的数据物理化了，SqlServer会在修改基础表时同步索引视图。但你不能直接同步视图内容。
我们知道在表上创建索引，能提高性能，相同，在视图也是一样，在视图上创建的第一个索引必须是唯一聚集索引，之后才可以创建其他的非聚集索引。
索引视图必须使用SCHEMABINDING选项，并且不能引用其他视图，只能引用基础表和UDF,而基础表和UDF必须使用两部分命名约定来引用(参见5.视图选项中的SCHEMABINDING选项)。
除了性能，你可能还会因为其他原因使用索引视图，比如在一张基础表中有一列我们要强制该列中已知值的唯一性，但是允许出现多次的NULL值，我们怎么办呢，我们首先想到的可能是用UNIQUE约束，但是UNIQUE会认为两个NULL值相等，那么这个不得不放弃了，那还有什么办法呢？
其实我们可以利用一个索引视图来完成这个任务，利用索引视图筛选所有非NULL的数据，那么这种索引将防止重复的已知值进入基础表，但允许多个NULL，因为NULL不是唯一索引的一部分，我们在向基础表中插入数据的时候，就利用索引视图的UNIQUE来限制我们的数据，来达到某列中强制已知值的唯一性的目的；
我们可以演示一下，首先创建一个基础表T2和一个索引视图V2:
复制代码 代码如下:
CREATE TABLE T2(col1 INT,col2 NVARCHAR(50))
CREATE VIEW V2
WITH SCHEMABINDING
AS
SELECT col1 FROM dbo.T2 WHERE col1 IS NOT NULL;
CREATE UNIQUE CLUSTERED INDEX idx_col1 ON dbo.V2(col1);
CREATE VIEW V2
WITH SCHEMABINDING
AS
SELECT col1 FROM dbo.T2 WHERE col1 IS NOT NULL;
CREATE UNIQUE CLUSTERED INDEX idx_col1 ON dbo.V2(col1);
然后我们向T2表中插入以下数据：
复制代码 代码如下:
INSERT INTO t2(col1,col2) VALUES(1,'2')
INSERT INTO t2(col1,col2) VALUES(1,'3')
INSERT INTO t2(col1,col2) VALUES(null,'4')
INSERT INTO t2(col1,col2) VALUES(null,'5')
INSERT INTO t2(col1,col2) VALUES(1,'3')
INSERT INTO t2(col1,col2) VALUES(null,'4')
INSERT INTO t2(col1,col2) VALUES(null,'5')
那么以上4条INSERT哪条会失败呢？答案是2。最后让我们SELECT 一下基础表T2，看实现我们开始那个要求了吗？
复制代码 代码如下:
SELECT * FROM t2
执行：
