Sql-server – Remove sub-string from WHERE clause

performancequery-performancesql server

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

SELECT DISTINCT
   ColumnBB,
   ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;

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

Best Answer

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

WHERE 
       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?

Related Question