Query returns different results

Question

I have 2 queries( i belive they must return same amount of rows:) ) :

1.

SELECT NAME
FROM myDataBase.myTable
WHERE CONTAINS(NAME, 'ABC')

2.

SELECT NAME
FROM myDataBase.myTable
WHERE NAME LIKE '%ABC%'

But in practice i have the following results(for example):

for the (1.) query

• first execution of (1.) query: 980 rows affected
• second execution of (1.) query: 996 rows affected
• ...
• N execution of (1.) query: 1000 rows affected

and for the (2.) query

• first execution of (2.) query: 1000 rows affected

So my question is: Why does it happen? What i'm doing wrong? :)

P.S. I'm new to Sql Server and and it's features, so i understand it's all my lack of theory, haven't found anything about this question, so decided to ask

Answer 1

CONTAINS is a full text predicate and looks for the word "ABC" in the full text index according to the current language's word breaker rules.

LIKE scans the column.

So LIKE would find 'XABCX' while CONTAINS would not.

Answer 2

CONTAINS works off of the Full Text Search engine.

It's entirely possible that the FTS indexes are being rebuilt in between runs of that query. If that is the case then I would expect the results you are seeing.

So, either don't use FTS or change the FTS population schedule to something a bit more acceptable.