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

Question

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.

score 2 · Answer 1 · edited May 23 '17 at 12:03

2

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

edited May 23 '17 at 12:03

Community

1
1

answered Oct 24 '11 at 17:06

Joe Stefanelli

132,803
19
237
235

score 0 · Answer 2 · answered Oct 24 '11 at 17:08

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.

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

2 Answers2