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 use
CHARINDEX. 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 his
ORlogic 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.