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
索引进行查找操作,提高了查询速度,如下图所示:
计算列索引的要求
要在计算列上创建索引,必须满足以下要求:
- 计算列表达式中涉及的函数必须与表具有相同的所有者。
- 计算列表达式必须具有确定性。这意味着对于给定的一组输入,表达式总是返回相同的结果。
- 计算列必须是精确数据类型,这意味着其表达式不能包含任何
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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器