Remove sub-string from WHERE clause 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?
If you can't modify the schema per db2's answer, then try:

WHERE 
       ColumnAA LIKE @VariableA + '%' 
   AND LEN(@VariableA) &gt;= 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?