How to use SARGable predicate in a WHERE clause?

Using substring or other functions in a WHERE clause may cause the database engine skip using the index on that column and perform a table scan. That would be very slow on a large table of millions of rows. In some cases the like matching function (sargable predicate) can be used without causing table scan. Example:

-- INDEX SEEK (fast) - SARGABLE WHERE clause predicate

SELECT * FROM Person.Person WHERE LastName = 'Nelson'

GO

-- INDEX SCAN (slow) - NON-SARGABLE WHERE clause predicate

SELECT * FROM Person.Person WHERE UPPER(LastName) = 'NELSON'

GO

------------

-- Table scan or index scan on a primary key index

-- no index use on ProductName

WHERE SUBSTRING(ProductName,1,3) = 'net' -- not sargable predicate

-- Index seek if there is an index on ProductName

WHERE ProductName LIKE 'net%'  -- sargable predicate

-- Table scan - no index use on OrderDate

WHERE year(OrderDate) = 2016 and month(OrderDate) = 1

-- Index seek

WHERE OrderDate >='2016-01-01' and OrderDate <'2016-02-01'

------------

-- Sargable predicate demo

------------

USE tempdb;

SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader

GO

CREATE INDEX idxOrderDate on SOH(SalesOrderID)

GO

-- COST 0.61 - TABLE SCAN - NOT SARGABLE PREDICATE due to function use

SELECT * FROM SOH

WHERE  FLOOR(SalesOrderID) = 20000

-- 0.0065 - INDEX SEEK - SARGABLE PREDICATE

SELECT * FROM SOH

WHERE  SalesOrderID = 20000

GO

DROP TABLE tempdb.dbo.SOH

GO

Related articles:

SARGable functions in SQL Server by Rob Farley

http://www.sqlusa.com/articles/query-optimization/