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?
You can't for RIGHT; indexes store the values in an order, so when you want to search for values which end with specific characters then the index can't be used as that's not the order the index is in.
Most likely the need to use RIGHT is a design flaw, and you are storing multiple different values in that column, rather than a single one. You probably should be using 2 (or more) columns for the separate values. Perhaps you should have a bit column named mypostfix, or you need varchar column that can store the value 'mypostfix'. You know you're requirements better than us to know which is correct
For LEFT, just use LIKE as trailing wildcards are SARGable:
SELECT <Your Columns>
FROM dbo.YourTable
WHERE YourColumn LIKE @SearchValue +'%';