问题描述
索引仅在 LIKE 操作中使用的 varchar 列是个好主意吗?根据我从查询分析中读取的信息，我从以下查询中获得:
Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query:
SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%'
我得到的估计子树成本"为 0.38，没有任何索引，0.14 有索引.如果已使用索引优化查询，这是用于分析的好指标吗?
I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzing if a query has been optimized with an index?
推荐答案
给定数据 'abcdefg'
Given the data 'abcdefg'
WHERE Column1 LIKE '%cde%' --can't use an index
WHERE Column1 LIKE 'abc%' --can use an index
WHERE Column1 Like '%defg' --can't use an index, but see note below
注意:如果您有需要 '%defg' 的重要查询，您可以使用持久计算列，您可以在其中 REVERSE() 列，然后对其进行索引.然后您可以查询:
Note: If you have important queries that require '%defg', you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on:
WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index
这篇关于SQL Server:索引列用在什么地方?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！