欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【SQL Server学习笔记】SQL SERVER 视图

程序员文章站 2024-02-22 23:27:46
...

视图包括:普通视图,索引视图,分布式分区视图。

关于视图的最佳实践:

     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

 

转载于:https://www.cnblogs.com/momogua/archive/2012/08/08/8304616.html