8.7 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索引进行查找操作,提高了查询速度,如下图所示:

计算列索引的要求

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

  • 计算列表达式中涉及的函数必须与表具有相同的所有者。
  • 计算列表达式必须具有确定性。这意味着对于给定的一组输入,表达式总是返回相同的结果。
  • 计算列必须是精确数据类型,这意味着其表达式不能包含任何FLOATREAL数据类型。
  • 计算列表达式的结果无法计算为TEXTNTEXTIMAGE数据类型,因为这样类型无法建索引。
  • 当使用CREAT TABLEALTER TABLE语句定义计算列时,必须设置ANSI_NULLS选项。此外,还必须设置选项ANSI_PADDINGANSI_WARNINGSARITHABORTQUERDED_IDENIFIERCONCAT_NULL_YIELDS_NULLS_NULL,并且必须将NUMERIC_ROUNDABORT设置为OFF
posted @   平元兄  阅读(182)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示