Sql Server關於indexed view索引檢視的總結

官方文件

索引檢視的結果集將儲存在資料庫中，就像表一樣，類似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.
得出結論：索引檢視不能跨庫建立，也就是索引檢視對應的基表只能在當前資料庫下面




索引檢視建立複製釋出的參考文件

對索引檢視建立複製釋出出現提示，要求把基表的表結構建立到訂閱端
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表結構肯定一樣