Sql Server关于indexed view索引视图的总结

官方文档https://docs.microsoft.com/zh-cn/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

索引视图的结果集将存储在数据库中，就像表一样，类似oracle的物化视图，索引视图在数据库中的存储方式与具有聚集索引的表的存储方式相同。 查询优化器可使用索引视图加快执行查询的速度。如果查询语句和索引视图定义一样，则该查询语句的执行计划会选用索引视图(和oracle的物化视图查询重写类似)，如果查询语句的执行计划不希望使用索引视图而要强制使用基表，则在查询语句后面加上OPTION (EXPAND VIEWS)

普通索引的结果不会存储在数据库，也就是说sys.partitions会存储索引视图的信息但是不会存储普通索引的信息，可以通过sys.partitions.object_id关联sys.allocation_units.container_id查出索引视图的占用的存储大小

总结：
1、创建索引视图必须设置SET选项
2、使用 WITH SCHEMABINDING 选项创建索引视图且select字句不能使用select (*)查询
3、必须为索引视图创建唯一性聚簇索引
4、基表会受到写入限制，比如对索引视图唯一性索引对应的基表列插入一条重复数据，会有报错
5、基表的列如果被索引视图引用，则该列不能执行任何修改
6、基表新增列后，索引视图不会显示，因为索引视图指定了列名
7、基表执行insert时，会同时把数据插入到索引视图
8、如果查询语句和索引视图定义一样，则该查询语句的执行计划会选用索引视图(和oracle的物化视图查询重写类似)，如果查询语句的执行计划不希望使用索引视图而要强制使用基表，则在查询语句后面加上OPTION (EXPAND VIEWS)
9、创建索引视图是，引用的基表名称只能有两级，即schemaname.tablename，也就是说索引视图不能跨库也不能跨linked server
10、如果表没有主键，可以使用索引视图来替代表进行复制发布订阅的操作，但是对索引视图创建复制发布订阅，则必须把发布端索引视图对于的基表建立到到订阅端，要不发布会报错Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.且索引视图的建立唯一性聚簇索引的字段不能为空，否则也会报错The clustered index on materialized view ‘[dbo].[view1]’ may not contain nullable columns if it is to be published using the transaction-based method.且建立的发布的时候必须修改exec sp_addarticle的@type = N’indexed view logbased’,默认是type = N’indexed view schema only’
11、对索引视图创建复制发布订阅后，发布端对象是索引视图，到了订阅端后对象默认变成了表，比如索引视图在发布端是dbo.view1，到了发布端这个dbo.view1不再是索引视图而是表，此时可以sp_addarticle的时候指定destination_table = N’基表名称’，这样发布端的索引视图就会同步到订阅端的索引视图对应的基表。比如索引视图在发布端名称是dbo.view1，它在发布端的基表是dbo.table1，sp_addarticle的时候指定destination_table = N’table1’，因为建立发布时要先在订阅端建立索引视图的基表dbo.table1，这样就会把发布端的索引视图dbo.view1数据同步到订阅端的dbo.table1，此时发布端的dbo.table1和订阅端的dbo.table1表结构可能不一样，但是发布端的dbo.view1和订阅端的dbo.table1表结构肯定一样

第一步
建表并插入数据

create table table1 (id1 int,id2 varchar(100), id3 char(10))
insert into table1 values(1,'1','1')

第二步
创建索引视图

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW view1 WITH SCHEMABINDING AS SELECT [id1],[id2],[id3]FROM [dbo].[table1]

–使用SCHEMABINDING选项时SELECT语句不能使用select ()查询，否则报错:Syntax '’ is not allowed in schema-bound objects.

第三步
对索引视图创建唯一性的聚集索引

CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON view1 (id1);

第四步
验证
1、对索引视图唯一性索引对应的原表列插入一条重复数据，会有报错：Cannot insert duplicate key row in object ‘dbo.view1’ with unique index ‘IDX_V1’. The duplicate key value is (4).
insert into table1 values(1,‘1’,‘1’)
2、对索引视图唯一性索引对应的原表列插入一条非重复数据，索引视图可以查看到具体的更新
insert into table1 values(2,‘2’,‘2’)
3、对基表新增列后，索引视图不会显示，因为索引视图指定了列名
alter table table1 add id4 nvarchar(10)
select * from view1 --没有id4这个字段
4、对基表用于索引视图的字段进行修改会报错
alter table table1 alter column id2 varchar(200)
报错信息：
The object ‘view1’ is dependent on column ‘id2’.
ALTER TABLE ALTER COLUMN id2 failed because one or more objects access this column.
5、对基表执行insert时，查看执行计划可以看到除了插入表table1，也插入索引视图view1
insert into [dbo].[table1] values (3,‘3’,‘3’,‘3’)

假如以上表table1存在于testdb1这个库，重新创建一个库testdb2，在testdb2下面创建一个索引视图看看

use testdb2
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW view1 WITH SCHEMABINDING AS SELECT [id1],[id2],[id3] FROM testdb1.[dbo].[table1];
GO
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON view1 (id1);

报错：Cannot schema bind view ‘view1’ because name ‘testdb1.dbo.table1’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
得出结论：索引视图不能跨库创建，也就是索引视图对应的基表只能在当前数据库下面

索引视图创建复制发布的参考文档https://www.virtual-dba.com/replicating-indexed-views-as-tables/

对索引视图创建复制发布出现提示，要求把基表的表结构建立到订阅端
Tables referenced by views are required
SQL Server requires that all tables referenced by published views and indexed views be available at the Subscriber. If the referenced tables are not published as articles in this publication, they must be created at the Subscriber manually.
The following views and indexed views are published in this publication:[dbo].[view1]

因为没有把基表的表结构建立到订阅端，导致订阅报错
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.

因为发布的索引视图的聚簇索引的字段为空，所以发布报错
The clustered index on materialized view ‘[dbo].[view1]’ may not contain nullable columns if it is to be published using the transaction-based method.
A publication must have at least one article before a subscription to it can be created.

实验结果：对索引视图创建复制发布订阅后，发布端对象是索引视图，到了订阅端后对象居然变成了表，比如索引视图在发布端是dbo.view1，到了订阅端这个dbo.view1不再是索引视图而是表，当然可以在sp_addarticle的时候指定destination_table = N’基表名称’，这样索引视图在发布端名称是dbo.view1，它在发布端的基表是dbo.table1，建立的发布时要先在订阅端建立索引视图的基表dbo.table1，这样就会把发布端的索引视图dbo.view1数据同步到订阅端的dbo.table1，此时发布端的dbo.table1和订阅端的dbo.table1表结构可能不一样，但是发布端的dbo.view1和订阅端的dbo.table1表结构肯定一样