I've got a situation where I must search single word.
For your scenario, which Query could be good from performance perspective?
Select Col1, Col2 from Table Where Col1 Like '%Search%'
Select Col1, Col2 from Table Where Col1 CONTAINS(Col1,'Search')
Full Text Searching (while using Consists of) is going to be faster/more effective than using As with wildcarding. Full Text Searching (FTS) includes a chance to define Full Text Indexes, which FTS may use. Dunno the reason why you wouldn't define a FTS index should you meant to make use of the functionality...
As with wildcarding around the left side (IE:
LIKE '%Search') can't make use of an index (presuming one is available for that column), ensuring a table scan. I've not examined &lifier in comparison, but regex has got the same pitfall. To explain,
LIKE '%Search' and
LIKE '%Search%' can not make use of an index
LIKE 'Search%' may use a catalog.
For any typical database, the Consists of search could be considerably faster presuming the right full text search index is made around the area being looked. The evaluation from the
LIKE operator generally does not make use of an index and therefore must read all of the data.