How can I make the LEFT
and RIGHT
functions in SQL sargable?
My T-SQL Lint (v1.15.3.0) yields an error on the following code:
SELECT DISTINCT B.BookID
FROM #Books B
WHERE RIGHT(B.[Name], 10) = '-mypostfix';
error non-sargable : Performing functions on filter clauses
or join predicates can cause performance problems.
I used a RIGHT
function to avoid a wildcard at the beginning of my string (e.g. LIKE %-mypostfix
), which seems like it would be less performant. However, after some research, I found that SQL can leverage its search index when using a wildcard. LEFT
and RIGHT
force the database to look through every row. Nonetheless, I am convinced that RIGHT
ought to be faster, especially for somewhat long strings.
Would I really need to use the wildcard strategy to avoid this linter error?
Peter Fyffe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.