Difference between using "Like" and using FullText Search "Contains"

This is SQL 2014.

I setup Full Text Search on 8 columns. The search is fast but I get different results when I use "Like" and when I use FTS's "Contains".  

I'll go step by step below.

1. This is how setup FTS
FTS1.png
FTS2.png
FTS3.png
FTS4.png
FTS5.png
2. This SQL brings back 28,243 rows and takes 1 min and 30 seconds. The data is correct. It uses Like

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaresult
 FROM dbo.camillasearchtest
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN ([LesseeCode] IS NULL) THEN
                                        CAST(1 AS BIT)
                                    ELSE
                                        CAST(0 AS BIT)
                                END
                               ) = (CASE
                                        WHEN (@p__linq__0 IS NULL) THEN
                                            CAST(1 AS BIT)
                                        ELSE
                                            CAST(0 AS BIT)
                                    END
                                   )
                              )
                      )
                 )
         )

Open in new window


3. This is FTS.  I tried both  
SET @searchtext ='019'
 SET @searchtext2 ='O19'

and

SET @searchtext ='%019%'
 SET @searchtext2 ='%O19%'

Takes about 45 seconds but brings back 60,000 something rows. Why is the difference? This isn't correct. What I can use to make sure it's like "Like"?

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaresult
 FROM dbo.camillasearchtest
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       ( CONTAINS(Sched,@searchtext))
		 OR 
		  (CONTAINS(SerialNo, @searchtext))
		  or
		  (CONTAINS(SerialNo, @searchtext2))
       
        OR (CONTAINS([CustomerRef], @searchtext ))
        OR (CONTAINS([Model], @searchtext ))
        OR (CONTAINS([Manufacturer], @searchtext))
        OR (CONTAINS ([SiteAddress] , @searchtext ))
               OR 
		(CONTAINS (customername, @searchtext ))
        OR (CONTAINS ([LesseeName], @searchtext ))
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN ([LesseeCode] IS NULL) THEN
                                        CAST(1 AS BIT)
                                    ELSE
                                        CAST(0 AS BIT)
                                END
                               ) = (CASE
                                        WHEN (@p__linq__0 IS NULL) THEN
                                            CAST(1 AS BIT)
                                        ELSE
                                            CAST(0 AS BIT)
                                    END
                                   )
                              )
                      )
                 )
         )

Open in new window


EDIT I made StopList "off" but still get extra rows.

EDIT: I tried this as well

  CONTAINS((Sched, SerialNo, CustomerRef,Model,Manufacturer,SiteAddress,customername,LesseeName), @searchtext)
or
  CONTAINS((Sched, SerialNo, CustomerRef,Model,Manufacturer,SiteAddress,customername,LesseeName), @searchtext2)

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
Have a read at:
https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017

I'm not certain why without really studying your code but there is a difference how LIKE and CONTAINS work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
Thanks, let me see if I can put together a sample table with some data. I didnt think of it when I posted the question. I'll post back.
AndyAinscowFreelance programmer / ConsultantCommented:
One thing you could do is construct two temporary tables, fill each with the results of one method then compare them.
You would expect all of the results from one method to be included in the other.  (Not that the result sets differ not just in quantity).  But from the differences you might notice something.  eg.  In one you have all results,  the other is case sensitive and you only obtain the capital 'O' results.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

CamilliaAuthor Commented:
I'll try that as well. Thanks, Andy. Also, I read the link you posted last night. I'll go thru it again. Thanks.
CamilliaAuthor Commented:
I'll go thru the results and see what I can find. You've been a great help. If I can't figure it out, I'll create an example. Will be easier to see some data.
CamilliaAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.