细说SQL Server中的视图

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)

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

运行该语句将会失败，回收到以下的提示：

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

但是，并不建议在视图中使用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

然后创建t1的视图v1:

create view v1
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

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

执行：