I thought full-text search would let me do exact phrase searching in a more optimized way than a LIKE predicate, but I'm reading that it doesn't do that exactly.
Is "LIKE" the most efficient way to search thousands of rows of TEXT fields in a table for a literal string?
It's got to be exact matching...
Apparently, CONTAINS is faster than a LIKE query...
(Profiling can be found on Page 19 of that presentation)
What version of SQL Server are you on? I would recommend replacing TEXT with VARCHAR(MAX), if you ever can (SQL Server 2005 and up).
What makes you say that full text won't work? How have you set up fulltext, and what do your fulltext queries look like?
Marc
LIKE(string%) will work faster if you have proper index on the column and you are looking for "string" only in the beginning of the value. You have to use LIKE(%string%) if the "string" might be in the middle of your value; table scan will be fired in this case and it's slow (slower than full-text search mostly). You can use the CONTAINS() function of full-text search for exact match.