在本教程中,将学习如何创建一个SQL Server索引视图,该视图在数据库中物理存储数据。

SQL Server索引视图简介

常规SQL Server视图是保存的查询,它们提供一些好处,例如查询简单性,业务逻辑一致性和安全性。 但是,它们不会提高基础查询性能。

与常规视图不同,索引视图是物理化视图,它们在物理上像表一样存储数据,因此如果使用得当,可能会提供一些性能优势。

要创建索引视图,请使用以下步骤:

首先,创建一个使用WITH SCHEMABINDING选项的视图,此选项将视图绑定到基础表的模式。

其次,在视图上创建唯一的聚簇索引,它实现了视图。

由于WITH SCHEMABINDING选项,如果要更改影响索引视图定义的基础表的结构,则必须先删除索引视图,然后再应用更改。

此外,SQL Server要求索引视图中的所有对象引用都包含两部分命名约定,即schema.object,并且所有引用的对象都在同一个数据库中。

当基础表的数据发生更改时,索引视图中的数据也会自动更新。 这会导致引用表的写入开销。 当写入基础表时,SQL Server也必须写入视图的索引。 因此,应该只针对具有频繁数据更新的表创建索引视图。

创建SQL Server索引视图示例

以下语句基于示例数据库中的production.products,production.brands和production.categories表的列创建索引视图:

sqlserver 视图建立唯一索引 sql server 索引视图_SQL

参考以下语法,创建一个索引视图:

CREATE VIEW product_master
WITH SCHEMABINDING
AS
SELECT
product_id,
product_name,
model_year,
list_price,
brand_name,
category_name
FROM
production.products p
INNER JOIN production.brands b
ON b.brand_id = p.brand_id
INNER JOIN production.categories c
ON c.category_id = p.category_id;

注意:视图名称后使用WITH SCHEMABINDING选项,其余部分与常规视图相同。

在为视图创建唯一的聚簇索引之前,通过查询常规视图中的数据并使用SET STATISTICS IO命令来检查查询I/O开销统计信息:

SET STATISTICS IO ON
GO
SELECT
*
FROM
production.product_master
ORDER BY
product_name;
GO

SQL Server返回以下查询I/O成本统计信息:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'products'. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'categories'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'brands'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

从输出中可以清楚地看到,SQL Server必须在返回结果集之前从三个相应的表中读取。

现在,为视图添加一个唯一的聚簇索引:

CREATE UNIQUE CLUSTERED INDEX

ucidx_product_id

ON production.product_master(product_id);

此语句实现了视图,使其在数据库中具有物理存在。还可以在视图的product_name列上添加非聚集索引:

CREATE NONCLUSTERED INDEX

ucidx_product_name

ON production.product_master(product_name);

现在,如果根据视图查询数据,应该可以看到统计信息已更改:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'product_master'. Scan count 1, logical reads 6, physical reads 1, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server现在不是从三个表中读取数据,而是直接从物化视图product_master读取数据。

请注意,此功能仅适用于SQL Server Enterprise Edition。 如果使用SQL Server Standard或Developer Edition,则必须直接在要使用视图查询的FROM子句中使用WITH(NOEXPAND)表提示,如下面的查询:

SELECT
*
FROM
production.product_master WITH (NOEXPAND)
ORDER BY
product_name;