T-SQL 之 视图

　　视图实际上就是一个存储查询，重点是可以筛选、组合和匹配来自基本表(或者其他视图)的数据，从而创建在很多方面像另一个基表那样起作用的对象。可以创建一个简单的查询，仅仅从一个表中选择几列，而忽略其他列；或者也可以创建一个复杂的查询，连接几个表，使的这些表看起来像一个表。

　　视图具体语法：

CREATE VIEW [schema_name].<view name> [(<column name list>)]
[WITH [ENCRYPTION][,SCHEMABINDING][,VIEW_METADATA]]
<SELECT statement>
WITH CHECK OPTION

一、视图定义

      视图是由一个查询所定义的虚拟表，它与物理表不同的是，视图中的数据没有物理表现形式，除非你为其创建一个索引；如果查询一个没有索引的视图，Sql Server实际访问的是基础表。

      如果你要创建一个视图，为其指定一个名称和查询即可。Sql Server只保存视图的元数据，用户描述这个对象，以及它所包含的列，安全，依赖等。当你查询视图时，无论是获取数据还是更新数据，Sql server都用视图的定义来访问基础表；

      视图在数据操作中扮演着许多重要的角色，比如可以利用视图访问经过筛选和处理的数据，而不是直接访问基础表，以及在一定程度上也保护了基础表。

      我们在创建视图的时候，也要遵守三个规则：

　　(1)不能在视图定义中指定ORDER BY ，除非定义中指定Top或For Xml ，并不建议在视图中使用ORDER BY ,因为视图表示一个虚拟表；

　　(2)所有的列必须有列名；

　　(3)这些所有的列名必须唯一；

　　对于视图表中在没有top或for xml说明的情况下，不能有Order by 语句，这是因为视图被认为是一个表，表是一个逻辑的实体，它的行是没有顺序的，所以建议在查询视图的时候，用ORDER BY。视图中所有列必须有列名。

　　示例：

CREATE VIEW columnNamePath_vw
AS
SELECT col_name,col_path 
From Nx_column

　　视图的目的是：向特定的用户屏蔽一部分信息，只显示他需要的信息。

　　视图与的工作方式与表很像，但是还是有一些不同之处。在对视图执行INSERT,UPDATE以及DELETE语句时，注意：

　　[1] 如果视图包含连接，在大多数情况下，除非使用INSTEAD OF触发器，否则不能对数据执行INSERT或DELETE操作。有时(只要只更新来自单个表的列)，UPDATE可以　　不使用INSTEAD OF触发器来工作，但是这需要一些规划，否则很快会遇到问题。

　　[2] 如果视图仅仅引用单个表，那么在表中的所有必需字段都在视图中或者有默认值的情况下，可以通过使用视图而不用INSTEAD OF触发器来对数据执行INSERT操作。其实对于单个表的视图来说，如果有一列没有出现在不具有默认值的视图中，那么如果想要允许INSERT操作，必须使用INSTEAD OF触发器。

　　[3] 通过WITH CHECK OPTION限制插入到视图中的内容，可在有限范围内限制是否可以在视图中插入或更新内容。　　

　　刷新视图

　　上面说过，视图会保存元数据，列，安全，以及依赖等信息，如果我们把基础表的架构更改了，并不会直接反映到视图上来；更改架构后，使用sp_refreshview存储过程刷新视图的元数据。

　　语法如下：

EXEC sp_refreshview view_name

　　更新视图

      视图是一个虚拟表，我们在查询视图的时候，实际上是对基础表的查询。视图不仅可以作为SELECT查询的目标，也可以作为修改语句的目标。当然，当你修改视图的时候，修改的时候是对基础表的修改，它就好像是一个代理。当然，如果不允许直接修改基础表，只允许修改视图，就可以限制你要公开的数据。这样，就可以对你的数据起到一定的保护作用,不过用到这种限制的时候很少。

　　在更新视图时的限制条件：

　　(1)只要视图有一列不能隐式获取值，就不能向视图中插入数据,如果列允许NULL、有默认值或者IDETITY属性，则说明它可以隐式获取值；

　　(2)如果视图包含连接，Update 或 Insert 语句只能影响连接的一端。也就是说，Update 或 Insert语句必须定义目标列列表，这些列只能属于连接的一端。不能从由连接查询定义的视图中删除数据；

　　(3)不能修改作为计算结果的列。如：标量表达式和聚合函数，SqlServer不会尝试改变数据库引擎的计算结果；

　　(4)如果在创建或修改视图时指定了 WITH CHECK OPTION 选项，与视图的查询筛选器有冲突的INSERT或UPDATE语句将被拒绝；

　　如果视图上定义了INSERT OF触发器，则违反这些限制的数据修改语句可以被执行。在INSERT OF触发器中你可以用自己的代码替换原始修改；

　　当你允许对有连接查询定义的视图执行修改的时候，一定要谨慎，比如一对多的关系。

　　视图选项

　　当你创建或修改视图时，可以指定一些选项，这些选项用户控制视图的行为和功能。

　　ENCRYPTION、SCHEMABINDING 和 VIEW_METADATA选项在视图头指定，CHECK OPTION选项则在查询之后指定，如下实例：

CREATE VIEW v2
WITH ENCRYPTION,SCHEMABINDING,VIEW_METADATA
AS
SELECT OrderID FROM dbo.Orders
WITH CHECK OPTION

　　

三、使用T-SQL编辑视图

　　在使用T-SQL编辑视图时要记住，这是在完全替换现有的视图。使用ALTER VIEW语句和CREATE VIEW语句的区别主要有以下几点：

　　[1] ALTER VIEW期望找到一个已有的视图，而CREATE则不是。

　　[2] ALTER VIEW保留了视图上已经建立的任何权限。

　　[3] ALTER VIEW保留了任何依赖信息。

　　要切记第二点，如果删除了视图，然后在CREATE，那么其效果与ALTER VIEW语句基本一样，只是权限信息要全部重建。

　　删除视图的语法如下：

DROP VIEW <view name>,[<view name>,[...n]]

 四、查看视图定义

　　有两种得到实际视图定义的方法：

　　1、sp_helptext ，示例如下：

EXEC sp_helptext PortlandAreaAddress_vw

　　2、sys.modules元数据函数，示例如下：

　　使用该函数的主要问题在于所有对象都用对象ID编码，对象ID是SQL Server跟踪事物的内部方法，他们是整型值而不是用于对象的名称。可以通过使用OBJECT_ID()函数来避开这个问题。

SELECT * 
　　FROM sys.sql_modules
　　WHERE　　object_id = OBJECT_ID('dbo.PortlandAreaAddress_vw')

五、视图选项

　　1、ENCRYPTION（加密）

　　如果你在构建任何类型的商业软件的时候，需要对视图进行加密的时候，这是一个不错的选项。如果未指定ENCRYPTION选项，SQLSERVER则以纯文本的形式保存用户定义的语句，如果指定了ENCRYPTION选项，对象的文本则会被混淆。

　　SQLSERVER提供了一个系统函数sp_helptext查看视图的文本，如果应用的ENCRYPTION选项，则会得到“The text for object ‘xx’ is encrypted”语句；

　　如果使用ALTER VIEW语句，那么意味着除了访问权限外，完全替换了现有的视图。那么意味着加密方法也被替换了。如果想对被改变的视图加密，那么必须在ALTER VIEW语句中使用WITH ENCRYPTION子句。　　

 　　示例：

　　ALTER VIEW CustomerOrders_vw
　　WITH ENCRYPTION
　　AS
　　SELECT.....

　　现在再对以上视图查看信息：

　　EXEC sp_helptext CustomerOrders_vw

　　SQL Server提示如下：对象'CustomerOrders_vw'的文本已加密。

　　注意：在使用WITH ENCRYPTION之前，必须先备份好源代码，一旦源代码被加密，就没有办法恢复。如果没有在其他地方存储代码，又需要改变代码只有重新编写。

　　2、SCHEMABINDING（模式绑定）

　　如果使用SCHEMABINDING选项创建视图，SQLSERVER将不允许删除基础表或修改被引用的列，防止在对底层对象修改时，使视图变得“孤立”。模式绑定实际上就是将视图所依赖的事物(表或者其他视图)“绑定”到视图。其重要意义在于除非首先删除模式绑定的视图，否则没有人可以修改那些对象(CREATE、ALTER)。

　　其作用如下：

　　(1) 可以防止修改底层对象时使用视图"孤立"。如删除了表，但没考虑到视图。

　　(2) 为了允许创建索引视图：如果想要在视图上创建索引，那么必须使用SCHEMABINDING选项来创建视图。

　　(3) 如果要创建一个模式绑定的用户自定义函数来引用视图，那么视图也必须是绑定的，应用这个选项要注意两点：

　　[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)
 

　　该视图的作用是查询所有有订单的客户的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

　　然后再执行下面的语句：

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的操作，就能看到，他提交到目标对象是视图；

　　个人总结：只要有VIEW_METADATA选项就有必要加上CHECK OPTION选项，而SCHEMABINDING选项，最好也要加上，防止你的视图“孤立”，而在索引视图中SCHEMABINDING选项是必须加上的。

　　5、索引视图

　　如果没有索引，视图中的数据不会有任何物理表现形似，如果加上索引，则就把视图中的数据物理化了，SqlServer会在修改基础表时同步索引视图。但你不能直接同步视图内容。

　　我们知道在表上创建索引，能提高性能，相同，在视图也是一样，在视图上创建的第一个索引必须是唯一聚集索引，之后才可以创建其他的非聚集索引。

　　索引视图必须使用 SCHEMABINDING 选项，并且不能引用其他视图，只能引用基础表和UDF,而基础表和UDF必须使用两部分命名约定来引用(参见视图选项中的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);

　　然后我们向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')

　　那么以上4条INSERT哪条会失败呢？答案是2。最后让我们SELECT 一下基础表T2，结果如下：

SELECT * FROM t2

 

执行：

　　

六、应用总结

　　视图一般用于以下情况：

　　[1] 过滤行；

　　[2] 保护敏感数据；

　　[3] 降低数据库呈现复杂性;

　　[4] 将多个物理数据库抽象为一个逻辑数据库;

　　视图注意事项总结：

　　[1] 避免基于视图构建视图-而应该将来自第一个视图的合适的查询信息应用到新的视图中。

　　[2] 记住使用WITH CHECK OPTION的视图提供了一些普通CHECK约束所不具备的灵活性

　　[3] 如果不希望其他人能看见你的源代码，就加密视图，但要记得备份未加密的代码；因为加密后就不能恢复为加密的代码了。

　　[4] 除了权限之外，使用ALTER VIEW意味着完全替换了现有的视图。这意味着如果要使被修改的视图里的加密和限制仍然是有效的，那么必须在ALTER语句里包含WITH ENCRYPTION和WITH CHECK OPTION子句。

　　[5] 使用sel_helptext显示视图支持的代码-避免使用系统表。

　　[6] 最小化用于生产查询的视图的用户-因为他们增加了额外的系统开销并且危害性能。

　　要知道，在默认的情况下，视图没有做什么特殊的事情。视图就好象一个查询那样从命令行运行(这里不存在任何形式的预先优化),这意味着在数据请求和将被交付的数据之间多加了一层开销。这表明视图绝不可能像 只是直接运行底层SELECT语句那样快。不过，视图存在有一个原因--这就是它的安全性或为用户所做的简化，在你的需要和开销之间权衡，找到最适合特定情况的解决方案。