细说Sql Server中的视图(下)

 

            1，什么是视图？

            2，为什么要用视图；

            3，视图中的ORDER BY;

            4，刷新视图；

            5，更新视图；

            6，视图选项；

            7，索引视图；

      细说Sql Server中的视图(上)     应大家要求已在“细说Sql Server中的视图(上)”中添加“为什么要用视图”一小节。

 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

    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)

 该视图的作用是查询所有有订单的客户的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的操作，就能看到，他提交到目标对象是视图；

还是那句话：如果客户端希望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);

 

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

 

执行：

哈哈实现了吧！

困了，睡觉了！

兴百放

Tag 标签: 更新视图，视图选项，索引视图，View，Sql Server View，加密视图

参考文献：《T-Sql程序设计》