SQL Server - 计算列上的索引?

2021-12-21 00:00:00 indexing calculated-columns sql-server-2008 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

  • 在该列上创建索引

  • Create an index on that column

    发出此查询以搜索字符串 Data:

    Issue this query to search for the string Data:

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

    第一个条件将使用索引进行粗过滤.

    The first condition will use an index for coarse filtering.

    第二个将确保所有前导字符(成为计算列中的尾随字符)都为零.

    The second will make sure that all leading characters (that became the trailing characters in the computed column) are nothing but zeros.

    有关性能详细信息，请参阅我博客中的此条目:

    See this entry in my blog for performance detail:

    • SQL Server:使用索引引导通配符匹配
    • SQL Server: leading wildcard match using an index

    更新

    如果您只想在 SUBSTRING 上建立索引而不更改架构，则可以选择创建视图.

    If you just want an index on SUBSTRING without changing your schema, creating a view is an option.

    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'

    • 相关文章