CODEWITHSUNDEEP
sql-server-2005
Dattatraya Kale
Dattatraya Kale

Reputation: 109

Why like query often takes more time than sub string if index is not applied on that column

I was trying out following 2 Sql queries. I used AdventureWorks 2005 sample database. 

/* Query #1 */
SELECT [SalesOrderID] 
  ,[SalesOrderDetailID]
  ,[CarrierTrackingNumber]
  ,[OrderQty]
  ,[ProductID]
  ,[SpecialOfferID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
  ,[LineTotal]
  ,[rowguid]
  ,[ModifiedDate]
FROM [AdventureWorks].[Sales].[SalesOrderDetail]
WHERE SUBSTRING([CarrierTrackingNumber],1,4) ='4911'

/* Query #2 */
SELECT [SalesOrderID]
  ,[SalesOrderDetailID]
  ,[CarrierTrackingNumber]
  ,[OrderQty]
  ,[ProductID]
  ,[SpecialOfferID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
  ,[LineTotal]
  ,[rowguid]
  ,[ModifiedDate]
FROM [AdventureWorks].[Sales].[SalesOrderDetail]
WHERE [CarrierTrackingNumber] LIKE '4911%'

and I found that time taken by #1 is less than #2 when there is no index on CarrierTrackingNumber. Can anybody tell me this kind of behavior? Does this mean sub string is faster than like for non indexed column?

However as soon as i applied the index #2 is too faster than #1 as we know like works faster on indexed column.

Upvotes: 0

Views: 162

Answers (2)

yfeldblum
yfeldblum

Reputation: 65435

Possible answer:

Substring computation and string equality comparisons are faster than like-pattern matching.

Left-side-constant like-pattern matching against an indexed string column is optimized to an index range scan, but no such optimization is implemented for prefix-substrings.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Query #1 is nonSARGable due to the substring function, so the index cannot help that query.

Upvotes: 2

Related Questions