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
