The thing I still haven't figured out is why I couldn't find anything on this topic in my searches. It seemed so obvious when I hit upon it, I can't believe that I'm the first person to surface these results. More likely my skills with Google need improvement.
Nope. I think your Google skills are probably just fine. You're probably one of the first to make this realization especially concerning leading "%" LIKEs.
I got taught a lesson (thank you Paul White for the correction) about this (although not specifically about LIKE) when I wrote a bad QOD (which has been corrected) and it really enhanced my understanding of how indexes worked and how to simplify code.
I was one of those that believed that ANY non-SARGable line of code in the WHERE clause was a very bad thing. To be sure, nothing beats a WHERE clause where all the lines in the WHERE clause are SARGable but sometimes the data in the table makes figuring that out a real pain or sometimes prevents it from happening at all despite all of the trickery in the world. For example, one might write a query against a customer table to return all customers that became customers in a given month. No problem. That makes for 100% SARGability in the presence of the correct index. If we add to that that the customer has a particular column that IS NULL or has a value of "0", you end up with either AND ISNULL(somecolumn,0) = 0 or you end up with (somecolumn IS NULL OR somecolumn = 0). Both suck... or so you would think. If you add "somecolumn" to the index (and sometimes without adding it), you still get a SEEK followed by a pretty fast range scan because the leading column of the index is based on the date. "Somecolumn" is just there for the ride and more quickly isolates the number of rows that the key lookup would have to do which makes it faster. Again, not as fast as something that's 100% SARGable but still very fast and faster than if you didn't add the column to the index, in most cases.
Of course, the right thing to do would be to make the table column NOT NULL so that you could, indeed, write 100% SARGable code but that's not always possible.
Again, thanks for the great article and all the testing you did. A lot of folks have no clue how long it takes to put something like this together and do the research and testing that it takes to actually be right at the same time. 🙂