I have a stored procedure that is using the SUBSTRING function in the WHERE clause.

WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;

How can I prevent an index scan for this query and make it execute quickly?

  • 1
    Do you have an index on ColumnAA? Also, why do you have a WITH (NOLOCK) in there? Mar 15, 2012 at 19:30
  • Why do you want to avoid (=prevent) an index scan? Mar 15, 2012 at 21:12
  • Garry, did any of the answers here solve your problem? Jun 8, 2012 at 16:58

4 Answers 4


If you can't modify the schema per db2's answer, then try:

       ColumnAA LIKE @VariableA + '%' 
   AND LEN(@VariableA) >= 17          -- safety check

Note that if @VariableA is shorter than 17 characters, then you may match unwanted strings using just a LIKE. For example, 'The quick brown fox jumped over the lazy dog.' is LIKE 'The%', but that doesn't satisfy the requirements of your original SUBSTRING query.

Aaron also brings up a good point that the DISTINCT may be the ultimate culprit. Why do you have it in there? Is it something that should be replaced with an appropriate unique index?


Are the substring ranges always the same? If so, you could turn that into a computed column, and index it.

  • The substring value is always the same. I cannot make any index or column changes to the DB. I need to be able to handle in the TSQL.
    – Garry B
    Mar 15, 2012 at 19:19

Replace it with like and see how it goes:

WHERE ColumnAA like @VariableA + '%'

like is faster than substring in this case (proof).

The newer predicate is not semantically the same as the old one, but as far as your requirements go the results should be as expected given you provide correct @VariableA values.

  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 9:16

I suspect the scan is caused by the DISTINCT (and the resulting sort). Does it go away without the DISTINCT? Wonder if you can fool the optimizer into dealing with that separately, e.g.

  -- the suggestion I gave you on twitter:
  SELECT ColumnBB, ColumnCC
  FROM dbo.TableAA
  WHERE ColumnAA LIKE @VariableA + '%'
SELECT ColumnBB, ColumnCC 
GROUP BY ColumnBB, ColumnCC;

I think the optimizer will see through this and won't necessarily short-circuit, but it could be worth a try.

If you want to improve performance, your best bet is to be open to schema changes. E.g. the clustered index scan may also be prevented if you add ColumnCC as an INCLUDE column to the index on ColumnBB. That will almost certainly help this query, but it may or may not be better for other queries.

  • You mean add both CC and BB as includes to the index on AA, right? Or, if the DISTINCT is necessary, add CC and BB as part of the key to the index on AA. Mar 15, 2012 at 20:27
  • Yep, sorry. Would have kept better track with real column names I suspect. Mar 15, 2012 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.