If you have queries that search for the first X number of characters in a column you might be using a LEFT.  Since LEFT is a function it makes the query predicate (WHERE) unsargable.  In other words an index cannot be efficiently used.

The demo query

In this query we are searching for the LEFT 7 characters that matches the input parameter:

exec sp_executesql @stmt=N'select * from CustomerAccount WHERE LEFT(AccountNbr,7) = @AccountNbr',
@params=N'@AccountNbr VARCHAR(40)', @AccountNbr='ACT0000'

You might notice I’m using sp_executesql to run this query.  I’m doing this since this is typically how your application will run parameterized SQL.  I’m also parameterizing the SQL to prove a point later in this post.

As you can see this query performs a scan as a result of the LEFT function being applied.

This took 21 seconds.

Modify it to use a LIKE

LIKE is an internal function to SQL Server which is sometimes sargable.  If we rewrite the query to use a LIKE instead of a left we get the following plan:

exec sp_executesql @stmt=N'select * from CustomerAccount WHERE AccountNbr LIKE @AccountNbr',
@params=N'@AccountNbr VARCHAR(40)', @AccountNbr='ACT0000%'

Wait, we’re getting a scan and this runs slower!  This took over 3 minutes.  You’re seeing one of the unintended issues with parameterizing LIKE queries.  I wrote a post on why this is an issue.  This seems to be fixed with newer versions (SQL 2022 and Azure SQL Database) that support the new parameter sniffing option.  You can see the details of that on this post.

So, if we make this non-parameterized SQL this fixes the problem and we now get an index seek and the query performs much better:

select * from CustomerAccount WHERE AccountNbr LIKE 'ACT0000%'

This took 1 millisecond to execute.

And if we have the parameter sniffing option enabled with the parameterized version of the query this is the plan:

This took 3 milliseconds to execute.

If you can’t modify the code to use a LIKE

There will be cases where you cannot modify the code to use a LIKE rather than a LEFT.  In this case you have the option of creating an indexed computed column:
ALTER TABLE CustomerAccount ADD AccountNbrLeft AS LEFT(AccountNbr, 7)

CREATE INDEX IX_CustomerAccountAccountNbrLeft
ON CustomerAccount (AccountNbrLeft)

After running the original unmodified query with the LEFT in it, the query performs a seek:

Of course there is a bit of overhead when adding computed columns, but you can’t get anything for free – usually the benefit outweighs the cost.

Final thoughts

You should avoid using LEFT in your predicate on columns.  Instead try and use a LIKE since it has a better chance of being sargable.  If you are using parameterized SQL, be aware of the gotchas I outlined!  Just a point of interest, when you look at LEFT in your execution plan it actually shows up as a SUBSTRING.