Use of functions on indexed columns in predicates

Use of functions on indexed columns which are used in predicates results in index not getting used for seek operation.

For example:

Use tempdb

set nocount on

go

IF OBJECT_ID(N'Customers', N'U') IS NOT NULL

   DROP TABLE dbo.Customers;

  

CREATE TABLE dbo.Customers (

 customer_nbr INT NOT NULL PRIMARY KEY,

 customer_name VARCHAR(35) NOT NULL);

CREATE NONCLUSTERED INDEX ix_customers

ON dbo.Customers(customer_name);

INSERT dbo.Customers VALUES(1, 'Joe');

INSERT dbo.Customers VALUES(2, 'Leo');

INSERT dbo.Customers VALUES(3, 'Dave');

INSERT dbo.Customers VALUES(4, 'Lenny');

INSERT dbo.Customers VALUES(5, 'Larry');

INSERT dbo.Customers VALUES(6, 'Lefty');

INSERT dbo.Customers VALUES(7, 'Lemur');

GO

If I want to write a query on this table to get all the customers whose first name starts with 'L'.

SELECT customer_name

FROM dbo.Customers

WHERE LEFT(customer_name, 1) = 'L';

Looking at the execution plan for above query. We can see index scan in here and not seek:

Now, what if we rewrite this query without the use of LEFT function as below:

SELECT customer_name

FROM dbo.Customers

WHERE customer_name LIKE 'L%';

Looking at the execution plan for above query shows us index seek and the output of query is same as before.

Functions on indexed column used in predicates makes them non-sargable and impacts performance.

Reference:

https://sqlbits.com/Sessions/Event14/Common_TSQL_Mistakes