How come SQL Server LIKE uses an index, but SQL Server CHARINDEX does not?

SQL Server maintains statistics on substrings in string columns in the form of tries that are usable by the LIKE query. However, its is not usable by the SQL Server CHARINDEX query See the String Summary Statistics section for more about this. A couple of important caveats are that any escaping of wildcards must be done with the proprietary square bracketing technique rather than the ESCAPE keyword and that for strings longer than 80 characters only the first and last 40 characters are used. will just use the standard guess for an inequality predicate that 30% of the rows will be returned. The LIKE query (in your case) presumably estimates much fewer rows will match the predicate. Note that the leading wildcard still prevents an index seek. An entire index is still scanned but it uses a different one that is narrower than the clustered index. The narrower index doesn't cover all columns used by the query so the second plan requires a key lookup to retrieve the missing columns. This plan is extremely unlikely to be chosen with the 30% estimate. SQL Server will consider it is cheaper to scan the entire clustered index and avoid that many lookups. See this article on the tipping point for additional examples. Example of SQL Server CHARINDEX The CHARINDEX() function searches for a substring in a string and returns the position. If the substring is not found, this function returns 0. Example:

    Search for "t" in string "Customer", and return position: