Aaron Bertrand has an article on performance testing various pattern matching techniques:
I wanted to prove to myself that the above procedure was undeniably better overall for all possible searches than any of the queries that use
ORconditionals, regardless of whether they useLIKE,LEFT/RIGHT,SUBSTRING, orCHARINDEX. I took David’s basic query structures and put them in stored procedures (with the caveat that I can’t really test “contains” without his input, and that I had to make hisORlogic a little more flexible to get the same number of rows), along with a version of my logic. I also planned to test the procedures with and without an index I would create on the search column, and under both a warm and a cold cache.
The conclusion is ambiguous. Read on for the full story.