答:SQLServer DBA 三十问之五:有关视图索引

5. 视图上我们能建索引吗,如果能建的话,会啥好处和坏处;视图上建索引和表上建索引有啥区别;

--------------------------------------------------------------------------------------------------------

能创建索引,好处:可以改善查询性能。 坏处:会相应的增加维护索引的开销
  区别:

1)对视图创建的第一个索引必须是唯一聚焦索引

2)该视图必须是使用schemabinding选项定义的。架构绑定
将视图与底层基表的架构进行绑定。

3)视图所引用的基表必须与该视图位于同一数据库中,并且与该视图有着相同的所有者

4)视图所引用的表和用户定义函数
在视图中必须通过两段式名称引用,不允许单段式,三段,和四段式名称

视图是一个保存的T-SQL查询,并不占用数据库的任何空间

索引视图是一个已被物化或保存在数据库中的视图。当基本表更新时,给视图建立的索引由数据库引擎保存和更新。当你的结果集返回许多行并且需要求总数时,索 引视图十分有用

SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

SET ARITHABORT ON

GO

SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO

--现在你可以建立自己的视图。为使事情更加简单,我建立一个崭新的视图。
CREATE VIEW dbo.vw_SalesByProduct_Indexed
WITH SCHEMABINDING
AS
SELECT
Product,
COUNT_BIG(*) AS ProductCount,
SUM(ISNULL(SalePrice,0)) AS TotalSales
FROM dbo.SalesHistory
GROUP BY Product
GO

--下面的脚本给我们的视图建立索引:
CREATE UNIQUE CLUSTERED INDEX
idx_SalesView ON vw_SalesByProduct_Indexed(Product)
--为表明已经给视图建立一个索引,并且它确实占用数据库的空间,运行下面的脚本查明聚集索引有多少行以及视图占用多少空间。
EXECUTE sp_spaceused 'vw_SalesByProduct_Indexed'
--下面的SELECT语句和以前的语句相同,只是这次它执行一个聚集索引搜索,这个过程完成得非常快。
SELECT
Product, TotalSales, ProductCount
FROM vw_SalesByProduct_Indexed
WHERE Product = 'Computer'

-----------------------------------------------

SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

SET ARITHABORT ON

GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO

--现在你可以建立自己的视图。为使事情更加简单,我建立一个崭新的视图。
CREATE VIEW dbo.vw_P2P_Invest_Indexed
WITH SCHEMABINDING
AS
SELECT
BorrowID,
COUNT_BIG(*) AS InvestCount,
SUM(ISNULL(InvestAmount,0)) AS TotalSales
FROM dbo.P2P_Invest
GROUP BY BorrowID
GO

drop view vw_P2P_Invest_Indexed

select top 100 * from vw_P2P_Invest_Indexed
where borrowid=4872333414374057429

CREATE UNIQUE CLUSTERED INDEX
idx_vw_P2P_Invest_BorrowID ON vw_P2P_Invest_Indexed(BorrowID)

--为表明已经给视图建立一个索引,并且它确实占用数据库的空间,运行下面的脚本查明聚集索引有多少行以及视图占用多少空间。
EXECUTE sp_spaceused 'vw_P2P_Invest_Indexed'

----------------------------------------------------------

在视图上创建索引需要三个条件:
一、视图必须绑定到架构。
要做到这点,在 CREATE VIEW
语句中,必须加上 WITH SCHEMABINDING,
如果是使用企业管理器,则在设计界面的空白处点击右键,属性,选中“绑定到架构”。
二、索引必须是唯一索引。

做到这点,在 CREATE INDEX 中必须指定 UNIQUE。
三、索引必须是聚集索引。
要做
到这点,在 CREATE INDEX 中必须指定 CLUSTERED。
例:
CREATE VIEW viewFoo WITH
SCHEMABINDING

AS
SELECT id...

CREATE
UNIQUE CLUSTERED INDEX
index_viewFoo ON viewFoo(id)

-----------------------------------------------------------------------------------------------

视图不能引用任何其它视图、导出表、行集函数或自查询,只能引用基表

视图引用的所有基表必须与视图位于同一个数据库中,并且所有者也与视图相同

必须使用   SCHEMABINDING   选项创建视图。SCHEMABINDING   将视图绑定到基础基表的架构。

如果视图定义中的   SELECT   语句指定了一个   GROUP   BY   子句,则唯一聚集索引的键只能引用在   GROUP   BY   子句中指定的列。

视图中的   SELECT   语句不能包含下列   Transact-SQL   语法元素:  
选择列表不能使用   *   或  
table_name.*   语法指定列。必须显式给出列名

在创建索引视图的select 语句中,不能存在重复的列名

视图不能包含一个外部链接或自链接,也就是说在链接表时只能使用INNER JOIN并且INNER JOIN前后不能使同一个表,不能使用LEFT(RIGHT) JOIN 或者 LEFT (RIGHT) OUTER JOIN

视图不能包含UNION子句、TOP子句、ORDER BY子句、Having子句、Rollup子句、Cube子句、compute子句、Compute By子句或Distinct关键字

视图不允许使用某些集合函数,如:Count(*)可以使用count_big(*)代替、avg()、max()、min()、stdev()、 stdevp()、var()或varp()等

如果视图包含一个Group By子句,那么他必须在Select列中包含count_big(*);

视图中的所有标和用户自定义的函数都必须使用两段式名来引用,即所有者.表或函数名称;

上一篇:[ES6] Generators


下一篇:【Qt】Qt Linguist介绍【转】