SQL Server - 计算列上的索引?

问题描述

我多次加入一个表，每次，我都根据其中一列的 SUBSTRING 的结果加入(或过滤)(它是一个字符串，但用零填充左，并且我不在乎最后四位数字).因此，即使此列已编入索引并且我的查询将使用该索引，它也会执行表扫描，因为 SUBSTRING 本身未编入索引，因此 SQL Server 在加入之前必须为每一行计算它.

I'm joining to a table dozens of different times, and every time, I join (or filter) based on the results of a SUBSTRING of one of the columns (it's a string, but left-padded with zeros, and I don't care about the last four digits). As a result, even though this column is indexed and my query would use the index, it does a table scan because the SUBSTRING itself isn't indexed, so SQL Server has to calculate it for every row before as it's joining.

我正在寻找有关如何加快此过程的任何想法.目前，表上有一个视图(它是一个SELECT * FROM"，只是为了给表一个友好的名称)，我正在考虑向计算的视图添加一列，然后对其进行索引.不过，我愿意接受其他建议 - 有什么想法吗?

I'm looking for any ideas on how to speed up this process. Currently, there's a view on the table (It's a "SELECT * FROM", just to give the table a friendly name), and I'm considering adding a column to the view that's computed, and then indexing that. I'm open to other suggestions, though - any thoughts?

更多细节:我应该一开始就分享这个.该表从我们的计费系统接收复制，因此编辑基础表以添加计算列不是一种选择.任何计算列都必须添加到表的视图中.此外，前导零并不总是前导零 - 它们有时是我不感兴趣的其他数据.我想真正的问题是我如何加入 VARCHAR 列中间的数据，而还使用索引?全文搜索?"

MORE DETAIL: I should have shared this to begin with. The table receives replication from our billing system, so editing the underlying table to add a computed column is not an option. Any computed column would have to be added to the view on the table. Also, the leading zeros aren't always leading zeros - they're sometimes other data that I'm not interested in. I suppose the real question is "How can I join to data in the middle of a VARCHAR column while also making use of an index? Full-text Search?"

澄清我的例子我正在简化，但基本上，假设我正在尝试使用以下值查找列中的值:

Clarifying my example I'm simplifying, but essentially, let's say I'm trying to look up values in a column with the following values:

00000012345MoreStuff
00000012345Whatever
19834212345
Houses12345837443GGD
00000023456MoreStuff

我对 SUBSTRING(7,5)="12345" 的行感兴趣，所以我想要第 1-4 行，而不是第 5 行.我的提议是在我的SELECT *"中添加一列" 包含此子字符串的视图，然后基于它进行索引.这更有意义吗?

I'm interested in rows where SUBSTRING(7,5)="12345", so I'd want rows 1-4, but not row 5. What I'm proposing is adding a column to my "SELECT *" view that has this substring in it, and then indexing based on that. Does that make more sense?

推荐答案

假设您的字段采用这种格式:

Assuming you have your fields in this format:

00Data0007
000000Data0011
0000Data0015

，您可以执行以下操作:

, you can do the following:

• 创建一个计算列:ndata AS RIGHT(REVERSE(data), LEN(data) - 4)

这会将您的列转换为以下内容:

This will transform your columns into the following:

ataD00
ataD000000
ataD0000

SELECT  *
FROM    mytable
WHERE   ndata LIKE N'ataD%'
        AND SUBSTRING(ndata, LEN(N'ataD') + 1, LEN(ndata)) = REPLICATE('0', LEN(ndata) - LEN('ataD'))

CREATE VIEW v_substring75
WITH SCHEMABINDING
AS
SELECT  s.id, s.data, SUBSTRING(data, 7, 5) AS substring75
FROM    mytable

CREATE UNIQUE CLUSTERED INDEX UX_substring75_substring_id ON (substring75, id)

SELECT  id, data
FROM    v_substring75
WHERE   substring75 = '12345'