Why I would bother using full text search?

Question

I am new to Full Text Search, I used the following query

Select * From Students Where FullName LIKE '%abc%'

Students table contains million records all random and look like this 'QZAQHIEK VABCNLRM KFFZJYUU'

It took only 2 seconds and resulted 1100 rows. If million record is searched in two seconds why I would bother using Full Text Search ?!! Did Like predicate used the Full Text Index as well?

Answer 1

No. LIKE does not make use of full text indexing. See here.

Computers are pretty darn fast these days but if you're seeing search results faster than you expect it's possible that you simply got back a cached result set because you executed the same query previously. To be sure you're not getting cached results you could use DBCC DROPCLEANBUFFERS. Take a look at this post for some SQL Server cache clearing options.

Excerpt from the linked page:

Comparing LIKE to Full-Text Search

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Answer 2

I think you have answered your own question, at least to your own satisfaction. If your prototyping produces results in an acceptable amount of time, and you are certain that caching does not explain the quick response (per Paul Sasik), by all means skip the overhead of full-text indexing and proceed with LIKE.