What is faster query ...where Fname = 'mark' or ...where Fname like 'mark'

Question

hi

What is faster query select...where Fname = 'mark' or select...where Fname like 'mark'

thank's

Answer 1

I believe the equality query, in general, is much faster.

This SQL Server Performance thread lists the operands in order of performance.

Answer 2

In this particular instance, they will most likely be the same because the LIKE has no wildcards. The optimiser detects it's trivial and makes it equality anyway.

Generally though, "=" will be quicker than "LIKE".

A simple example

--Different plans because name has index.
--uses bookmark lookup but 40% of batch
SELECT * FROM sys.columns WHERE name = 'offset'
--uses clustered index scan 60% of batch
SELECT * FROM sys.columns WHERE name LIKE 'offset'
GO



--same plan, 50% each of batch
SELECT * FROM sys.server_permissions WHERE class_desc = 'ENDPOINT'
SELECT * FROM sys.server_permissions WHERE class_desc LIKE 'ENDPOINT'