SQL Server - 计算列的索引？ [英] SQL Server - index on a computed column?
问题描述
我正在加入一个表几十个不同的时间，每次，我根据其中一列的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列的中间加入数据还使用索引？全文搜索？
澄清我的例子
我正在简化，但实际上，假设我试图在列中查找具有以下值的值：
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?
推荐答案
假设您的字段格式为：
00Data0007
000000Data0011
0000Data0015
，您可以执行以下操作：
, you can do the following:
-
创建计算列：
ndata AS RIGHT （REVERSE（数据），LEN（数据） - 4）
这会将您的列转换为以下内容：
This will transform your columns into the following:
ataD00
ataD000000
ataD0000
在该列上创建索引
Create an index on that column
发出此查询以搜索字符串数据：
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: 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'
这篇关于SQL Server - 计算列的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
