Erik Darling shows how you can use a computed column to speed up %something searches:
We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching.
What’s back-searching? It’s a word I just made up.
The easiest example to think about and demo is Social Security Numbers.
One security requirement is often to give the last four.
Obviously running this for a search
WHERE ssn LIKE '%0000'would perform badly over large data sets.
One of the trickiest parts of performance tuning is understanding that the way people want to see data is not necessarily the way you should store the data.