Why  doesn't this Full Text Search bring a row back?

Camillia
Camillia used Ask the Experts™
on
This is a related question to this Related question

I created a sample. I'll go step by step. Please scroll down to my 'Edit' note below. I figured something on this that I don't understand.

1. This is the table with 2 rows and the Full Text Search. You can run it to create the sample table with data

/****** Object:  Table [dbo].[camillasearchtest]    Script Date: 11/18/2018 6:59:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[camillasearchtest](
	[RowId] [int] IDENTITY(1,1) NOT NULL,
	[ScheduleID] [int] NOT NULL,
	[ID] [bigint] NOT NULL,
	[SerialNo] [nvarchar](64) NULL,
	[Manufacturer] [nvarchar](64) NULL,
	[Model] [nvarchar](250) NULL,
	[Vendor] [nvarchar](64) NULL,
	[Quantity] [int] NULL,
	[DateAccepted] [date] NULL,
	[DateReturned] [date] NULL,
	[SiteAddress] [nvarchar](256) NULL,
	[SiteAddress2] [nvarchar](256) NULL,
	[SiteCity] [nvarchar](256) NULL,
	[SiteState] [nvarchar](256) NULL,
	[SiteZip] [nvarchar](256) NULL,
	[SiteCountryCode] [nchar](3) NULL,
	[BillName] [nvarchar](256) NULL,
	[BillToAttn] [nvarchar](40) NULL,
	[BillAddress] [nvarchar](256) NULL,
	[BillAddress2] [nvarchar](256) NULL,
	[BillCity] [nvarchar](256) NULL,
	[BillState] [nvarchar](256) NULL,
	[BillZip] [nvarchar](256) NULL,
	[BillCountryCode] [nchar](3) NULL,
	[RemitName] [nvarchar](256) NULL,
	[RemitToAttn] [nvarchar](40) NULL,
	[RemitAddress] [nvarchar](256) NULL,
	[RemitAddress2] [nvarchar](256) NULL,
	[RemitCity] [nvarchar](256) NULL,
	[RemitState] [nvarchar](256) NULL,
	[RemitZip] [nvarchar](256) NULL,
	[RemitCountryCode] [nchar](3) NULL,
	[Rent] [money] NULL,
	[Tax] [money] NULL,
	[AssetInfo] [nvarchar](max) NULL,
	[DateIn] [date] NULL,
	[CustomerRef] [nvarchar](100) NULL,
	[UdFilterValue] [nvarchar](50) NULL,
	[Udf1] [nvarchar](50) NULL,
	[Udf2] [nvarchar](50) NULL,
	[Udf3] [nvarchar](50) NULL,
	[Udf4] [nvarchar](50) NULL,
	[Udf5] [nvarchar](50) NULL,
	[Udf6] [nvarchar](50) NULL,
	[Udf7] [nvarchar](50) NULL,
	[Udf8] [nvarchar](50) NULL,
	[Udf9] [nvarchar](50) NULL,
	[Udf10] [nvarchar](50) NULL,
	[CSIDataLocked] [bit] NOT NULL,
	[CustomerDataLocked] [bit] NOT NULL,
	[InventoryId] [int] NULL,
	[Deleted] [bit] NOT NULL,
	[DateDeleted] [date] NULL,
	[Description] [nvarchar](1000) NULL,
	[Returned] [bit] NULL,
	[UnitInsurance] [decimal](18, 2) NULL,
	[SiteLocale] [nvarchar](256) NULL,
	[SiteRegion] [nvarchar](256) NULL,
	[BillLocale] [nvarchar](256) NULL,
	[BillRegion] [nvarchar](256) NULL,
	[RemitLocale] [nvarchar](256) NULL,
	[RemitRegion] [nvarchar](256) NULL,
	[ExternalID] [nvarchar](50) NULL,
	[GeographyCoordinates] [nvarchar](100) NULL,
	[NonCsiLeased] [bit] NOT NULL,
	[LastModified] [datetime] NULL,
	[ExternalSourceId] [int] NULL,
	[Category] [nvarchar](50) NULL,
	[DateCreated] [datetime] NULL,
	[ID1] [int] NULL,
	[Sched] [nvarchar](32) NULL,
	[MasterLeaseID] [int] NULL,
	[DateLeaseStarted] [date] NULL,
	[Term] [int] NULL,
	[TermUnitCode] [nchar](1) NULL,
	[DateLeaseEnds] [date] NULL,
	[Billing] [nvarchar](40) NULL,
	[Rent1] [money] NULL,
	[Tax1] [money] NULL,
	[LesseeCode] [nvarchar](64) NULL,
	[LesseeName] [nvarchar](100) NULL,
	[Insurance] [nvarchar](64) NULL,
	[FreqCode] [nvarchar](2) NULL,
	[PayFormCode] [nvarchar](2) NULL,
	[CurrCode] [nvarchar](10) NULL,
	[Ordering] [nvarchar](30) NULL,
	[Location] [nvarchar](100) NULL,
	[DateDeleted1] [date] NULL,
	[AccountRepID] [int] NULL,
	[Deleted1] [bit] NULL,
	[International] [bit] NULL,
	[Status] [int] NULL,
	[DeleteDate] [date] NULL,
	[LastModified1] [datetime] NULL,
	[ExternalID1] [nvarchar](50) NULL,
	[ExternalSourceId1] [int] NULL,
	[DebtRate] [decimal](18, 2) NULL,
	[NewHardwareCost] [decimal](18, 2) NULL,
	[OriginalEquipmentCost] [decimal](18, 2) NULL,
	[Margin] [decimal](18, 2) NULL,
	[ResidualValue] [decimal](18, 2) NULL,
	[FinalPV] [decimal](18, 2) NULL,
	[InterimRent] [decimal](18, 2) NULL,
	[VendorInvoiceTotal] [decimal](18, 2) NULL,
	[ScheduleInsurance] [decimal](18, 2) NULL,
	[Comment] [nvarchar](4000) NULL,
	[USGAAPType] [nvarchar](20) NULL,
	[ID2] [int] NULL,
	[LeaseNo] [nvarchar](80) NULL,
	[AccountRepID1] [int] NULL,
	[CustomerName] [nvarchar](100) NULL,
	[UdFilterLabel] [nvarchar](32) NULL,
	[UdfLabel1] [nvarchar](32) NULL,
	[UdfLabel2] [nvarchar](32) NULL,
	[UdfLabel3] [nvarchar](32) NULL,
	[UdfLabel4] [nvarchar](32) NULL,
	[UdfLabel5] [nvarchar](32) NULL,
	[UdfLabel6] [nvarchar](32) NULL,
	[UdfLabel7] [nvarchar](32) NULL,
	[UdfLabel8] [nvarchar](32) NULL,
	[UdfLabel9] [nvarchar](32) NULL,
	[UdfLabel10] [nvarchar](32) NULL,
	[WhenUpdated] [datetime] NULL,
	[Deleted2] [bit] NULL,
	[DeletedDate] [date] NULL,
	[PortfolioID] [int] NULL,
	[Active] [bit] NULL,
	[International1] [bit] NULL,
	[LastModified2] [datetime] NULL,
	[ExternalID2] [nvarchar](50) NULL,
	[ExternalSourceId2] [int] NULL,
	[ShowArchivedData] [bit] NULL,
 CONSTRAINT [PK_camillasearchtest] PRIMARY KEY CLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[camillasearchtest] ON 
GO
INSERT [dbo].[camillasearchtest] ([RowId], [ScheduleID], [ID], [SerialNo], [Manufacturer], [Model], [Vendor], [Quantity], [DateAccepted], [DateReturned], [SiteAddress], [SiteAddress2], [SiteCity], [SiteState], [SiteZip], [SiteCountryCode], [BillName], [BillToAttn], [BillAddress], [BillAddress2], [BillCity], [BillState], [BillZip], [BillCountryCode], [RemitName], [RemitToAttn], [RemitAddress], [RemitAddress2], [RemitCity], [RemitState], [RemitZip], [RemitCountryCode], [Rent], [Tax], [AssetInfo], [DateIn], [CustomerRef], [UdFilterValue], [Udf1], [Udf2], [Udf3], [Udf4], [Udf5], [Udf6], [Udf7], [Udf8], [Udf9], [Udf10], [CSIDataLocked], [CustomerDataLocked], [InventoryId], [Deleted], [DateDeleted], [Description], [Returned], [UnitInsurance], [SiteLocale], [SiteRegion], [BillLocale], [BillRegion], [RemitLocale], [RemitRegion], [ExternalID], [GeographyCoordinates], [NonCsiLeased], [LastModified], [ExternalSourceId], [Category], [DateCreated], [ID1], [Sched], [MasterLeaseID], [DateLeaseStarted], [Term], [TermUnitCode], [DateLeaseEnds], [Billing], [Rent1], [Tax1], [LesseeCode], [LesseeName], [Insurance], [FreqCode], [PayFormCode], [CurrCode], [Ordering], [Location], [DateDeleted1], [AccountRepID], [Deleted1], [International], [Status], [DeleteDate], [LastModified1], [ExternalID1], [ExternalSourceId1], [DebtRate], [NewHardwareCost], [OriginalEquipmentCost], [Margin], [ResidualValue], [FinalPV], [InterimRent], [VendorInvoiceTotal], [ScheduleInsurance], [Comment], [USGAAPType], [ID2], [LeaseNo], [AccountRepID1], [CustomerName], [UdFilterLabel], [UdfLabel1], [UdfLabel2], [UdfLabel3], [UdfLabel4], [UdfLabel5], [UdfLabel6], [UdfLabel7], [UdfLabel8], [UdfLabel9], [UdfLabel10], [WhenUpdated], [Deleted2], [DeletedDate], [PortfolioID], [Active], [International1], [LastModified2], [ExternalID2], [ExternalSourceId2], [ShowArchivedData]) VALUES (1, 4628, 245511, N'XEE431019', N'XEROX', N'WC5735P COPIER', N'CONNECTICUT BUSINESS SYST', NULL, CAST(N'2013-01-01' AS Date), NULL, N'71 Haynes St', NULL, N'Manchester', N'CT', N'06040-4131', N'USA', N'Prospect ECHN, Inc.', N'Nick Jamieson', N'71 Haynes Street', N'', N'Manchester', N'CT', N'06040', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 73.4600, 0.0000, N'(1) Main Box|(1) XEROX COPIER 1GB MEM|(1) XEROX COPIER FAX CARD|(1) XEROX COPIER FINISHER|(1) XEROX COPIER SCANNER', CAST(N'2018-11-18' AS Date), N'GRD-BEVHLTH-6722', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 408477, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'245511', NULL, 0, CAST(N'2018-11-18T07:40:30.677' AS DateTime), NULL, NULL, NULL, 4628, N'033', 131, CAST(N'2013-01-01' AS Date), 63, NULL, CAST(N'2018-03-31' AS Date), N'MONTHLY IN ADVANCE', 14957.3200, 0.0000, N'LSEEAST', N'Prospect ECHN, Inc.', NULL, N'M ', N'AD', N'USD', NULL, NULL, NULL, 79, 0, 0, 2, NULL, CAST(N'2017-12-02T05:27:18.900' AS DateTime), NULL, NULL, NULL, NULL, CAST(766764.45 AS Decimal(18, 2)), NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, NULL, NULL, NULL, NULL, 131, N'208494', 79, N'Prospect ECHN, Inc.', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(N'2013-02-04T18:10:41.620' AS DateTime), 0, NULL, 1, 1, 0, CAST(N'2017-11-22T05:27:06.890' AS DateTime), N'180', NULL, 0)
GO
INSERT [dbo].[camillasearchtest] ([RowId], [ScheduleID], [ID], [SerialNo], [Manufacturer], [Model], [Vendor], [Quantity], [DateAccepted], [DateReturned], [SiteAddress], [SiteAddress2], [SiteCity], [SiteState], [SiteZip], [SiteCountryCode], [BillName], [BillToAttn], [BillAddress], [BillAddress2], [BillCity], [BillState], [BillZip], [BillCountryCode], [RemitName], [RemitToAttn], [RemitAddress], [RemitAddress2], [RemitCity], [RemitState], [RemitZip], [RemitCountryCode], [Rent], [Tax], [AssetInfo], [DateIn], [CustomerRef], [UdFilterValue], [Udf1], [Udf2], [Udf3], [Udf4], [Udf5], [Udf6], [Udf7], [Udf8], [Udf9], [Udf10], [CSIDataLocked], [CustomerDataLocked], [InventoryId], [Deleted], [DateDeleted], [Description], [Returned], [UnitInsurance], [SiteLocale], [SiteRegion], [BillLocale], [BillRegion], [RemitLocale], [RemitRegion], [ExternalID], [GeographyCoordinates], [NonCsiLeased], [LastModified], [ExternalSourceId], [Category], [DateCreated], [ID1], [Sched], [MasterLeaseID], [DateLeaseStarted], [Term], [TermUnitCode], [DateLeaseEnds], [Billing], [Rent1], [Tax1], [LesseeCode], [LesseeName], [Insurance], [FreqCode], [PayFormCode], [CurrCode], [Ordering], [Location], [DateDeleted1], [AccountRepID], [Deleted1], [International], [Status], [DeleteDate], [LastModified1], [ExternalID1], [ExternalSourceId1], [DebtRate], [NewHardwareCost], [OriginalEquipmentCost], [Margin], [ResidualValue], [FinalPV], [InterimRent], [VendorInvoiceTotal], [ScheduleInsurance], [Comment], [USGAAPType], [ID2], [LeaseNo], [AccountRepID1], [CustomerName], [UdFilterLabel], [UdfLabel1], [UdfLabel2], [UdfLabel3], [UdfLabel4], [UdfLabel5], [UdfLabel6], [UdfLabel7], [UdfLabel8], [UdfLabel9], [UdfLabel10], [WhenUpdated], [Deleted2], [DeletedDate], [PortfolioID], [Active], [International1], [LastModified2], [ExternalID2], [ExternalSourceId2], [ShowArchivedData]) VALUES (48, 13540, 383777, N'22ADK101941', N'WYSE', N'C90LEW DT VIA/1GHZ', N'IBM CANADA LTD.', NULL, CAST(N'2014-07-01' AS Date), NULL, N'700 LAWRENCE AVE W', NULL, N'TORONTO', N'ON', N'M6A 3B4', N'CAN', N'Canadian Tire Dealers'' Association', N'Connie Bateman', N'171 Ambassador Drive
Unit 5', N'', N'Mississauga', N'ON', N'L5T 2J1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 20.6300, 2.6800, N'(1) Main Box', CAST(N'2018-11-18' AS Date), N'19', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 654882, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'383777', NULL, 0, CAST(N'2018-11-18T07:40:30.677' AS DateTime), NULL, NULL, NULL, 13540, N'011AI', 243, CAST(N'2014-07-01' AS Date), 60, NULL, CAST(N'2019-06-30' AS Date), N'QUARTERLY IN ADVANCE', 110496.3300, 13767.4100, N'LSECANADIANT', N'Canadian Tire Dealers'' Association', NULL, N'Q ', N'AD', N'CAD', NULL, NULL, NULL, NULL, 0, 0, 2, NULL, CAST(N'2018-09-19T05:18:38.423' AS DateTime), NULL, NULL, NULL, NULL, CAST(1702940.73 AS Decimal(18, 2)), NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, NULL, NULL, NULL, NULL, 243, N'243772', 88, N'Canadian Tire Dealers'' Association', N'', N'Store Number', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(N'2013-02-04T18:10:41.620' AS DateTime), 0, NULL, 2, 1, 0, CAST(N'2015-06-22T20:15:19.990' AS DateTime), N'350', NULL, 0)
GO
SET IDENTITY_INSERT [dbo].[camillasearchtest] OFF
GO
/****** Object:  FullTextIndex     Script Date: 11/18/2018 6:59:50 PM ******/
CREATE FULLTEXT INDEX ON [dbo].[camillasearchtest](
[CustomerName] LANGUAGE 'English', 
[CustomerRef] LANGUAGE 'English', 
[LesseeName] LANGUAGE 'English', 
[Manufacturer] LANGUAGE 'English', 
[Model] LANGUAGE 'English', 
[Sched] LANGUAGE 'English', 
[SerialNo] LANGUAGE 'English', 
[SiteAddress] LANGUAGE 'English')
KEY INDEX [PK_camillasearchtest]ON ([FTSAssetSearchTest], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = OFF)

GO

Open in new window


2. I use "Like" and it brings back the 2 rows.

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 camillaNoFTS
 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. Now, I use "Contains" because I want to use Full Text Search but howcome the row with SerialNo XEE431019 is not brought back?

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%' 
 SET @searchtext2 ='%O19%'

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

-- SET @searchtext = '019*' 
-- SET @searchtext2 ='O19*'

 SELECT * 
--INTO camillaFTS
 FROM dbo.camillasearchtest
 WHERE
 
  --rowid = 1 AND --***remove this
  (

--  Contains ((Sched,SerialNo, CustomerRef,Model,Manufacturer,SiteAddress,customername,LesseeName), @searchtext)
--or
--  CONTAINS((Sched, SerialNo, CustomerRef,Model,Manufacturer,SiteAddress,customername,LesseeName), @searchtext2)
       ( 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


It brings back row with SerialNo 22ADK101941 but not XEE431019 I'll keep working on this and I figure it out, I'll post back.

Edit - More Info I just commented out all the "Where" clause except for (Contains([CustomerRef], @searchtext )) This causes the select I have to bring back one row. But why doesn't the SQL look at the "SerialNo" column as well?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> I just commented out all the "Where" clause except for (Contains([CustomerRef], @searchtext )) This causes the select I have to bring back one row. But why doesn't the SQL look at the "SerialNo" column as well?

Yes, that works as expected because SQL Server Full Text Search is supposed to work on a word or phrase and not on characters within a word..
CONTAINS can search for:
    A word or phrase.
    The prefix of a word or phrase.
    A word near another word.
    A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
    A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").
https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017

If you still want to find characters inside your Serial No., then you have to use LIKE clause only..
Like clause wont use full text search, correct? I'm using FTS to speed up my search.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Yes, LIKE Search and FTS are separate algorithms and to bring both the records on Serial No, you need to use LIKE clause only..
If you search based upon the entire word, then you can use FTS for faster search 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!

Thanks, Raja. Let me read and understand. I'll post back.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Sure, kindly let me know for more questions..
FREETEXT won't work either, looks like it. I think I have to use "Like" and NOT use FTS for my search.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes, you might need to use LIKE for this scenario..
Thanks, Raja. I just told my manager about this. "Like" is slow and looks like it ignotes indexes but it is what it is now.
Thanks for your help.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to assist!!

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