8.7 SQL Server计算列上的索引

 

 

SQL Server计算列上的索引

 

目录

• SQL Server计算列上的索引
  • 简介
  • 计算列索引的要求

 

简介

有如下客户表：

查找居住在garry.espinoza的客户：

	SELECT
	first_name,
	last_name,
	email
	FROM
	sales.customers
	WHERE
	SUBSTRING(email, 0,
	CHARINDEX('@', email, 0)
	) = 'garry.espinoza';

输出中清楚显示，查询优化器需要扫描整个聚集索引来定位客户，效率很低。

使用过 Oracle 或 PostgreSQL的可能知道 Oracle 支持基于函数的索引，而 PostgreSQL 支持基于表达式的索引。这些类型的索引允许对函数或表达式的结果进行索引，这将提高其 WHERE 子句包含函数和表达式的查询的性能。

在SQL Server中，可以使用计算列上的索引来实现类似基于函数的索引的效果：

• 首先，基于WHERE子句上的表达式创建一个计算列。
• 其次，为计算列创建非聚集索引。

例如，要根据客户电子邮件地址的本地部分（@部分）搜索客户，可以使用以下步骤：
先给表customers创建一个计算列：

	ALTER TABLE sales.customers
	ADD
	email_local_part AS
	SUBSTRING(email,
	0,
	CHARINDEX('@', email, 0)
	);

然后再在这个计算列上创建索引：

	CREATE INDEX ix_cust_email_local_part
	ON sales.customers(email_local_part);

现在，您可以使用email_local_part列而不是WHERE子句中的表达式来通过电子邮件地址的本地部分查找客户：

	SELECT
	first_name,
	last_name,
	email
	FROM
	sales.customers
	WHERE
	email_local_part = 'garry.espinoza';

查询优化器使用ix_cust_email_local_part索引进行查找操作，提高了查询速度，如下图所示：

计算列索引的要求

要在计算列上创建索引，必须满足以下要求：

• 计算列表达式中涉及的函数必须与表具有相同的所有者。
• 计算列表达式必须具有确定性。这意味着对于给定的一组输入，表达式总是返回相同的结果。
• 计算列必须是精确数据类型，这意味着其表达式不能包含任何FLOAT或REAL数据类型。
• 计算列表达式的结果无法计算为TEXT、NTEXT或IMAGE数据类型，因为这样类型无法建索引。
• 当使用CREAT TABLE或ALTER TABLE语句定义计算列时，必须设置ANSI_NULLS选项。此外，还必须设置选项ANSI_PADDING，ANSI_WARNINGS，ARITHABORT，QUERDED_IDENIFIER和CONCAT_NULL_YIELDS_NULLS_NULL，并且必须将NUMERIC_ROUNDABORT设置为OFF。

 

分类: SQL Server

标签: SQL Server , 数据库 , SQL Server高级