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屋！