【SQL Server学习笔记】SQL SERVER 视图
视图包括：普通视图，索引视图，分布式分区视图。
关于视图的最佳实践：
A、对视图进行性能优化的过程和普通的select查询语句的优化过程是一样的。
B、不要在一个视图的定义中调用另一个视图。
C、如果可能的话尽量使用存储过程而不是视图。因为存储过程能重用执行计划，性能会有提升，存储过程还能减少网络流量，并且实现复制的业务逻辑，而且与视图相比，编码限制更少。
1、普通视图
视图允许一个select语句中最多定义1024列，不可以在视图定义中使用某些select元素，包括into，option，compute，compute by，表变量，临时表，在使用了top关键字时才能使用order by子句。
--1.1创建视图
create view dbo.v_wcT
as
select wcId,
wcV,
wcDate
from wcT
go
--1.2使用视图
select * from dbo.v_wcT
--视图元数据
--2.1视图定义
select object_id,
definition
from sys.sql_modules
where object_id = object_id('dbo.v_wcT')
--2.2视图的架构
select s.name, --视图的架构
v.name --视图的名称
from sys.views v
inner join sys.schemas s
on v.schema_id = s.schema_id
--2.3视图中列的信息
select v.name, --视图名称
c.name --视图中的列名
from sys.views v
inner join sys.columns c
on v.object_id = c.object_id
--3.当视图引用的表对象修改后,视图的元数据可能过时了,
--如:更改了视图中引用列的宽度,在刷新之前,元数据还是修改前的宽度
--3.1
alter table wcT
alter column wcV varchar(50) not null
--3.2查看元数据
select v.name,
c.name,
c.max_length --发现元数据没有变化,还是100
from sys.views v
inner join sys.columns c
on v.object_id = c.object_id
where c.name = 'wcV'
--3.3刷新视图元数据
exec sp_refreshview
@viewname = 'dbo.v_wcT'
--3.4再次查看元数据
select v.name,
c.name,
c.max_length --发现这里已经变为50
from sys.views v
inner join sys.columns c
on v.object_id = c.object_id
where c.name = 'wcV'
--3.5适用于:存储过程,触发器,用户定义函数,视图
exec sp_refreshsqlmodule
@name = 'dbo.v_wcT'
--4.修改视图
alter view dbo.v_wcT
as
select wcId,
wcV,
wcDate
from wcT
where wcId > 2
go
--5.1通过视图来insert,update,delete
--视图的定义中不能有:聚合函数,group by,distinct,having
--只有当基础数据表对查询编写者不可见的时候,才应该使用视图来插入,更新数据
insert into dbo.v_wcT(wcId,wcV,wcDate)
values(8,'wc','2010-10-01')
--查看新增的数据
select * from wcT where wcId = 8
--5.2通过视图删除数据
delete from dbo.v_wcT where wcId = 8
--6.1视图加密
alter view dbo.v_wcT
with encryption
as
select wcId,
wcV,
wcDate
from wcT
where wcId > 2
go
--6.2发现定义是NULL
select object_id,
definition
from sys.sql_modules
where object_id = object_id('dbo.v_wcT')
--7.删除视图
drop view dbo.v_wcT
2、索引视图
sql server中有2个概念，一个是索引，一个是视图。那么如果把这两个结合起来，就是索引视图.
原来我们都知道索引，就是按照某个字段，建立一个物理的对象，实际最在于硬盘上的数据。
而视图，其实就是一个虚拟表，就是一个定义，实际上并不存在数据，要查询视图，都是通过定义中的基本表来访问数据的。
通过索引视图，其实就是按照索引的定义，建立了一个索引，也就是把虚拟的东西，给实体化了，实体化为实际的存在于硬盘上的数据。
但是索引视图，有不少限制条件：
比如:
•索引视图涉及的基本表必须ANSI_NULLS设置为ON
•索引视图必须设置ANSI_NULLS和QUOTED_INDETIFIER为ON
•索引视图只能引用基本表
•SCHEMABINDING必须设置
•定义索引视图时必须使用Schema.ViewName这样的全名
•索引视图中不能有子查询
•avg,max,min,stdev,stdevp,var,varp这些聚合函数不能用
那么，在实际上工作中，我也想用，但是实际去用的时候，都是由于上面的各种限制，最后都没办法用，所以，总的来说，用的不多。
视图与定义它的底层select查询相比，没有性能上的优势。通过给视图添加索引，确实能提升访问视图时的性能。
一旦在视图上创建了索引，那么用于物化视图的数据就像表的聚集索引那样保存。在创建了唯一的聚集索引后，还可以进一步创建其他的非聚集索引，基础表不会受到这些视图索引的影响，因为这些视图索引是独立的基础对象。
如果查询优化器觉得索引视图有用，即使sql语句显式引用了视图底层基础表而不是视图本身，优化器也会使用索引视图。对于视图可以通过指定noexpand表提示来强制查询优化器不展开成基础表，而使用索引视图；对于表不能指定noexpand提示，只能由优化器来选择。
--1.1创建索引视图
create view dbo.v_index_wcT
with schemabinding
as
select wcid,
wcV,
wcDate
from dbo.wcT
go
--1.2查询视图,查看io统计信息
set statistics io on
select wcv
from dbo.wcT
order by wcV
--2.1在索引视图上创建唯一聚集索引
create unique clustered index idx_v_index_wcT
on dbo.v_index_wcT(wcid)
--2.2创建非聚集索引
create nonclustered index idx_v_index_wcT_wcV
on dbo.v_index_wcT(wcV)
--再次查询视图,查看IO统计信息
select wcv
from dbo.v_index_wcT
order by wcV
--强制使用索引视图
select wcv
from dbo.v_index_wcT with(noexpand)
order by wcV
select wcv
from dbo.wcT --访问基础表
order by wcV
3、分布式分区视图
--第1个实例
use master
go
exec sp_addlinkedserver
@server='PC0627JVC',
@srvproduct='SQL SERVER'
exec sp_serveroption
@server = 'PC0627JVC',
@optname = 'lazy schema validation',
@optvalue = 'true'
create database wcA
USE wcA
GO
create table dbo.wcA
(webHitID uniqueidentifier not null,
webSite varchar(20),
hitDate datetime not null,
check (website = 'A'),
constraint pk_webHits primary key (webHitID,webSite))
--第2个实例
use master
go
exec sp_addlinkedserver
@server='PC0627JVC\MSSQLSERVER2008',
@srvproduct='SQL SERVER'
exec sp_serveroption
@server = 'PC0627JVC\MSSQLSERVER2008',
@optname = 'lazy schema validation',
@optvalue = 'true'
create database wcB
USE WCB
GO
create table dbo.wcB
(webHitID uniqueidentifier not null,
webSite varchar(20),
hitDate datetime not null,
check (website = 'B'),
constraint pk_webHits primary key (webHitID,webSite))
--第1个实例
create view dbo.v_webhits
as
select webhitid,
website,
hitDate
from wcA.dbo.wcA
union all
select webhitid,
website,
hitDate
from [PC0627JVC].wcB.dbo.wcB
go
--第2个实例
create view dbo.v_webhits
as
select webhitid,
website,
hitDate
from wcB.dbo.wcB
union all
select webhitid,
website,
hitDate
from [PC0627JVC\MSSQLSERVER2008].wcA.dbo.wcA
go
/*===================================================
如果有错误，那么会回滚所有事务，
不过这里每个insert语句都是一个事务,
所以当第一个insert执行成功,会提交,而第2个有错误,那么会回滚,
所以应该写在显式的事务中
======================================================*/
set xact_abort on
insert into dbo.v_webhits(webhitid,website,hitdate)
values(NEWID(),'B',GETDATE())
insert into dbo.v_webhits(webhitid,website,hitdate)
values(NEWID(),'c',GETDATE())
--更好的写法
set xact_abort on
begin transaction
insert into dbo.v_webhits(webhitid,website,hitdate)
values(NEWID(),'B',GETDATE())
insert into dbo.v_webhits(webhitid,website,hitdate)
values(NEWID(),'c',GETDATE())
commit transaction
