TheEsisia TheEsisia - 9 days ago 6
SQL Question

Which one is preffered in a WHERE clause: 'wild cards' or 'String Functions'?

I know that both Wild Cards and Functions must be avoided in the WHERE clauses if possible. My question is: which one is yet better in terms of performance, assuming that we have to choose between one of them? More specifically, I have the following two queries and I want to know which type of query is preferred in general:

select FirstName from person.person where FirstName like 'S%'

select FirstName from person.person where LEFT(FirstName,1) = 'S'


PS. I have checked the execution time of both queries and there was no noticeable difference in this specific case. Here are the execution plans:

enter image description here

enter image description here

enter image description here

This is the code for the index IX_Person_LastName_FirstName_MiddleName is defined:

USE [AdventureWorks2012]
GO

/****** Object: Index [IX_Person_LastName_FirstName_MiddleName] Script Date: 11/30/2016 12:53:05 PM ******/
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO;


Here is a few links I found about this issue, though they do not completely answer my question:

AvoidFunctionsInWereClause_1

AvoidFunctionsInWereClause_2

AvoidLikeClause

Answer

Both queries are doing index scan so those are OK. But we dont know how you setup your index or table sizes.

First version can use a basic index on firstName because the % is at the end.

 FirstName like 'S%'

 FirstName Like '%S%' -- cant use index

Second Version cant use the index on firstName, but you can create a calculated index for LEFT(FirstName,1) that is why you should show us your index.

This is article have the best tips to create index. MySQL index TIPS