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

Camillia
Camillia used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Freelance programmer / Consultant
Commented:
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.
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 / Consultant
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I'll try that as well. Thanks, Andy. Also, I read the link you posted last night. I'll go thru it again. Thanks.
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial