SQL Server like statement issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. Now I have two patterns to implement the same function (to see if zoo1 or zoo2 or zoo3 column contains some text, in pattern 1, I merge content of zoo1, zoo2 and zoo3 to form a new column called zoo), I think the first pattern is always of better performance (I have made some experiment) from my experiment. But I am not sure whether I am correct, and what is the internal reason why pattern 1 is always of better performance?

Pattern 1:

Select foo, goo from tablefoo where zoo like LIKE '%'+@Query+'%'

Pattern 2 (zoo is a column which I merge the content of column zoo1, zoo2 and zoo3 to generate):

Select foo, goo from tablefoo where (zoo1 like LIKE '%'+@Query+'%') OR (zoo2 like LIKE '%'+@Query+'%') or (zoo3 like LIKE '%'+@Query+'%')

thanks in advance, George

Answer 1

OR almost always kills performance.

In this case, it's 3 columns to scan vs 1 column to scan.

In both cases, because you have a leading % then an index won't be used anyway (it may be scan because it's covers the zoo% columns)

The 1 column is merely less bad than the 3 column OR query. Not "better".