SQLServer之建立索引视图

时间 2019-12-05 标签 sqlserver 建立索引视图

索引视图建立注意事项

对视图建立的第一个索引必须是惟一汇集索引。建立惟一汇集索引后，能够建立更多非汇集索引。为视图建立惟一汇集索引能够提升查询性能，由于视图在数据库中的存储方式与具备汇集索引的表的存储方式相同。查询优化器可以使用索引视图加快执行查询的速度。要使优化器考虑将该视图做为替换，并不须要在查询中引用该视图。数据库

索引视图中列的 large_value_types_out_of_row 选项的设置继承的是基表中相应列的设置。此值是使用 sp_tableoption设置的。从表达式组成的列的默认设置为 0。这意味着大值类型存储在行内。服务器

能够对已分区表建立索引视图，并能够由其自行分区。架构

若要防止数据库引擎使用索引视图，请在查询中包含 OPTION (EXPAND VIEWS) 提示。此外，任何所列选项设置不正确均会阻止优化器使用视图上的索引。有关 OPTION (EXPAND VIEWS) 提示的详细信息，请参阅 SELECT (Transact-SQL)。分布式

若删除视图，该视图的全部索引也将被删除。若删除汇集索引，视图的全部非汇集索引和自动建立的统计信息也将被删除。视图中用户建立的统计信息受到维护。非汇集索引能够分别删除。删除视图的汇集索引将删除存储的结果集，而且优化器将从新像处理标准视图那样处理视图。函数

能够禁用表和视图的索引。禁用表的汇集索引时，与该表关联的视图的索引也将被禁用。工具

索引视图建立要求

建立索引视图须要执行下列步骤而且这些步骤对于成功实现索引视图而言很是重要：性能

验证是否视图中将引用的全部现有表的 SET 选项都正确。优化
在建立任意表和视图以前，验证会话的 SET 选项设置是否正确。ui
验证视图定义是否为肯定性的。加密
使用 WITH SCHEMABINDING 选项建立视图。
为视图建立惟一的汇集索引。

索引视图所需的 SET 选项

若是执行查询时启用不一样的 SET 选项，则在数据库引擎中对同一表达式求值会产生不一样结果。例如，将 SET 选项 CONCAT_NULL_YIELDS_NULL 设置为 ON 后，表达式 ' abc ' + NULL 会返回值 NULL。但将 CONCAT_NULL_YIEDS_NULL 设置为 OFF 后，同一表达式会生成 ' abc '。

为了确保可以正确维护视图并返回一致结果，索引视图须要多个 SET 选项具备固定值。下表中的 SET 选项必须设置中显示的值为RequiredValue列出现如下状况时：

建立视图和视图上的后续索引。
在建立表时，在视图中引用的基表。
对构成该索引视图的任何表执行了任何插入、更新或删除操做。此要求包括大容量复制、复制和分布式查询等操做。

查询优化器使用该索引视图生成查询计划。

SET 选项	必需的值	默认服务器值	，则“默认” OLE DB 和 ODBC 值	，则“默认” DB-Library 值
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS*	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

*将 ANSI_WARNINGS 设置为 ON 隐式将 ARITHABORT 设置为 ON。

若是使用的是 OLE DB 或 ODBC 服务器链接，则惟一必需要修改的值是 ARITHABORT 设置。必须使用 sp_configure 在服务器级别或使用 SET 命令从应用程序中正确设置全部 DB-Library 值。极力建议在服务器的任一数据库中建立计算列的第一个索引视图或索引后，尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。

肯定性视图

索引视图的定义必须是肯定性的。若是选择列表中的全部表达式、WHERE 和 GROUP BY 子句都具备肯定性，则视图也具备肯定性。在使用特定的输入值集对肯定性表达式求值时，它们始终返回相同的结果。只有肯定性函数能够加入肯定性表达式。例如，DATEADD 函数是肯定性函数，由于对于其三个参数的任何给定参数值集它老是返回相同的结果。 GETDATE 不是肯定性函数，由于老是使用相同的参数调用它，而它在每次执行时返回结果都不一样。

要肯定视图列是否为肯定性列，请使用 COLUMNPROPERTY 函数的 IsDeterministic 属性。使用 COLUMNPROPERTY 函数的 IsPrecise 属性肯定具备架构绑定的视图中的肯定性列是否为精确列。若是为 TRUE，则 COLUMNPROPERTY 返回 1；若是为 FALSE，则返回 0；若是输入无效，则返回 NULL。这意味着该列不是肯定性列，也不是精确列。

即便是肯定性表达式，若是其中包含浮点表达式，则准确结果也会取决于处理器体系结构或微代码的版本。为了确保数据完整性，此类表达式只能做为索引视图的非键列加入。不包含浮点表达式的肯定性表达式称为精确表达式。只有精确的肯定性表达式才能加入键列，并包含在索引视图的 WHERE 或 GROUP BY 子句中。

其余要求

除对 SET 选项和肯定性函数的要求外，还必须知足下列要求：

执行 CREATE INDEX 的用户必须是视图全部者。
建立索引时，IGNORE_DUP_KEY 选项必须设置为 OFF（默认设置）。
在视图定义中，表必须由两部分组成的名称（即 schema.tablename**）引用。
必须已使用 WITH SCHEMABINDING 选项建立了在视图中引用的用户定义函数。
视图中引用的任何用户定义函数都必须由两部分组成的名称（即 schema.function**）引用。
用户定义函数的数据访问属性必须为 NO SQL，外部访问属性必须是 NO。
公共语言运行时 (CLR) 功能能够出如今视图的选择列表中，但不能做为汇集索引键定义的一部分。 CLR 函数不能出如今视图的 WHERE 子句中或视图中的 JOIN 运算的 ON 子句中。

在视图定义中使用的 CLR 函数和 CLR 用户定义类型方法必须具备下表所示的属性设置。

“属性”	注意
DETERMINISTIC = TRUE	必须显式声明为 Microsoft .NET Framework 方法的属性。
PRECISE = TRUE	必须显式声明为 .NET Framework 方法的属性。
DATA ACCESS = NO SQL	经过将 DataAccess 属性设置为 DataAccessKind.None 并将 SystemDataAccess 属性设置为 SystemDataAccessKind.None 来肯定。
EXTERNAL ACCESS = NO	对于 CLR 例程，该属性的默认设置为 NO。

必须使用 WITH SCHEMABINDING 选项建立视图。
视图必须仅引用与视图位于同一数据库中的基表。视图没法引用其余视图。

视图定义中的 SELECT 语句不能包含下列 Transact-SQL 元素：


COUNT	ROWSET 函数（OPENDATASOURCE、OPENQUERY、OPENROWSET 和 OPENXML）	OUTER 联接（LEFT、RIGHT 或 FULL）
派生表（经过在 FROM 子句中指定 SELECT 语句来定义）	自联接	经过使用 SELECT * 或 SELECT table_name来指定列。*
DISTINCT	STDEV、STDEVP、VAR、VARP 或 AVG	公用表表达式 (CTE)
`float`*`text`， `ntext`， `image`， `XML`，或`filestream`列	子查询	包括排名或聚合开窗函数的 OVER 子句
全文谓词（CONTAIN、FREETEXT）	引用可为 Null 的表达式的 SUM 函数	ORDER BY
CLR 用户定义聚合函数	返回页首	CUBE、ROLLUP 或 GROUPING SETS 运算符
MIN、MAX	UNION、EXCEPT 或 INTERSECT 运算符	TABLESAMPLE
表变量	OUTER APPLY 或 CROSS APPLY	PIVOT、UNPIVOT
稀疏列集	内联或多语句表值函数	OFFSET
CHECKSUM_AGG

*索引的视图能够包含float列; 可是，不能在汇集的索引键中包含此类列。

若是存在 GROUP BY，则 VIEW 定义必须包含 COUNT_BIG(*)，而且不得包含 HAVING。这些 GROUP BY 限制仅适用于索引视图定义。即便一个索引视图不知足这些 GROUP BY 限制，查询也能够在其执行计划中使用该视图。
若是视图定义包含 GROUP BY 子句，则惟一汇集索引的键只能引用 GROUP BY 子句中指定的列。

使用SSMS数据库管理工具建立索引视图

一、链接数据库，选择数据库，展开数据库-》右键视图-》选择新建视图。

二、在添加表弹出框-》选择要建立视图的表、视图、函数、或者同义词等-》点击添加-》添加完成后选择关闭。

三、在关系图窗格中-》选择表与表之间关联的数据列-》选择列的其余排序或筛选条件。

四、右键点击空白处-》选择属性。

五、在视图属性窗格-》绑定到架构选择是-》非重复值选择是。

六、点击保存或者ctrl+s-》查看新建立的视图。

七、在对象资源管理器窗口-》展开视图-》选择视图-》右键点击索引-》选择新建索引-》选择汇集索引。

八、在新建索引弹出框-》选择索引数据列-》索引建立步骤能够参考本博主的建立索引博文-》点击肯定（建立惟一汇集索引以后才能建立非汇集索引）。

九、在对象资源管理器中查看视图中的索引。

十、刷新视图-》能够建立非汇集索引，步骤同建立汇集索引（此处省略建立非汇集索引）。

十一、点击保存或者ctrl+s-》刷新视图-》查看结果。

十二、使用视图。

使用T-SQL脚本建立索引视图

语法：

--声明数据库引用
use 数据库;
go

--判断视图是否存在，若是存在则删除
if exists(select * from sys.views where name=视图名称)
drop view 视图名称;
go

--建立视图
create
view

--视图所属架构的名称。
--[schema_name][.]

--视图名称。视图名称必须符合有关标识符的规则。能够选择是否指定视图全部者名称。
[dbo][.]视图名称

--视图中的列使用的名称。仅在下列状况下须要列名：列是从算术表达式、函数或常量派生的；两个或更多的列可能会具备相同的名称（一般是因为联接的缘由）；视图中的某个列的指定名称不一样于其派生来源列的名称。还能够在 SELECT 语句中分配列名。
--若是未指定 column，则视图列将得到与 SELECT 语句中的列相同的名称。
--column

with

--适用范围： SQL Server 2008 到 SQL Server 2017 和 Azure SQL Database。
--对 sys.syscomments 表中包含 CREATE VIEW 语句文本的项进行加密。使用 WITH ENCRYPTION 可防止在 SQL Server 复制过程当中发布视图。
--encryption,

--将视图绑定到基础表的架构。若是指定了 SCHEMABINDING，则不能按照将影响视图定义的方式修改基表或表。必须首先修改或删除视图定义自己，才能删除将要修改的表的依赖关系。
--使用 SCHEMABINDING 时，select_statement 必须包含所引用的表、视图或用户定义函数的两部分名称 (schema.object)。全部被引用对象都必须在同一个数据库内。
--不能删除参与了使用 SCHEMABINDING 子句建立的视图的视图或表，除非该视图已被删除或更改而再也不具备架构绑定。不然，数据库引擎将引起错误。
--另外，若是对参与具备架构绑定的视图的表执行 ALTER TABLE 语句，而这些语句又会影响视图定义，则这些语句将会失败。
schemabinding

--指定为引用视图的查询请求浏览模式的元数据时， SQL Server 实例将向 DB-Library、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不返回基表的元数据信息。
--浏览模式元数据是 SQL Server 实例向这些客户端 API 返回的附加元数据。若是使用此元数据，客户端 API 将能够实现可更新客户端游标。浏览模式的元数据包含结果集中的列所属的基表的相关信息。
--对于使用 VIEW_METADATA 建立的视图，浏览模式的元数据在描述结果集内视图中的列时，将返回视图名，而不返回基表名。
--当使用 WITH VIEW_METADATA 建立视图时，若是该视图具备 INSTEAD OF INSERT 或 INSTEAD OF UPDATE 触发器，则视图的全部列（timestamp 列除外）均可更新。有关可更新视图的详细信息，请参阅“备注”。
--view_metadata

--指定视图要执行的操做。
as
select_statement
go

--建立索引详情请参考索引博客
if not exists (select * from sys.indexes where name=索引名称)
--设置索引
create
unique
clustered
index
索引名称
on
dbo.视图名
(列名 [ asc | desc],列名 [ asc | desc],......);
go

示例：本示例演示在视图上建立一个惟一汇集索引。

--声明数据库引用
use testss;
go

--判断视图是否存在，若是存在则删除
if exists(select * from sys.views where name='indexview1')
drop view indexview1;
go