SQL SERVER 中的 SUBSTRING 与 LEFT [英] SUBSTRING vs LEFT in SQL SERVER

问题描述

我在这里发布了一个问题，但没有人回答，所以我尝试了专注于使我的查询变慢的原因，并且我想到了一个问题.哪个更快更高效?LEFT 还是 SUBSTRING?

I posted a question here, but no one answer so I tried to focus what's making my query slow and a question came into my mind. Which one is faster and more efficient? LEFT or SUBSTRING?

推荐答案

SQL Server 是一个数据库.你不会问哪个字符串处理函数更快"的问题.您会问哪些可以使用索引?"的问题.和我有所需的索引吗?".都是关于数据访问，因为磁盘很慢，而不是关于移动 CPU 寄存器.

SQL Server is a database. You dod not ask questions of which string processing function is 'faster'. You ask the questions 'which can use an index?' and 'do I have the required index?'. Is all about data access, because disks are sloooooow, not about shifting CPU registers.

那么，哪个可以使用索引?(哪个是sargable>?).理论上 LEFT 可以使用索引，但实际上通常不会.SUBSTRING 不能.代替 SUBSTRING 使用 全文.

So, Which can use an index? (which one is sargable?). In theory LEFT could use an index, but in practice it usually does not. SUBSTRING cannot. Instead of SUBSTRING use Full Text.

设计您的数据模型以利用 sargable 表达式，并相应地索引.这就是它的全部内容，没有灵丹妙药.避免扫描.

Design your data model to take advantage of sargable expressions, index accordingly. That's all there is to it, there is no magic bullet. Avoid scans.

这篇关于SQL SERVER 中的 SUBSTRING 与 LEFT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！