SQL Server：索引列中使用的类似？ [英] SQL Server: Index columns used in like?

问题描述

索引仅在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：索引列中使用的类似？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！