DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to avoid using substring in a WHERE clause?

Using substring 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:

-- 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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE