FreeTextTable returns no records

Question

I have a flat table to perform Full Text Search and even the simplest search doesn't work. I have tried numerous things and I am getting same result. However surprising thing is that similar Full Text Search works on other tables in the same database.

Below is my table structure, I have also added Full Text Index details for quick reference.

Below is the search result of my query, I have tried multiple things without any luck, the query only works when I provide exact value from the column. Search results are same even when I use one column i.e. Fname only in my query.

I may be missing some basic thing but I am not sure what that thing is. Below is the create table script for it.

CREATE TABLE [dbo].[SampleTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NOT NULL,
    [VID] [int] NOT NULL,
    [FID] [int] NOT NULL,
    [TID] [int] NOT NULL,
    [FName] [nvarchar](200) NOT NULL,
    [IName] [nvarchar](200) NOT NULL,
    [Contents] [nvarchar](max) NOT NULL,
    [LatestVersion] [int] NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
    [ID] 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]

insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'climate.c','climate_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateTreeLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateGeneralLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gage.c','gage_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validateAquifer','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','lid_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidProc','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidGroup','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','link_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','conduit_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','pump_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','orifice_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','weir_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','node_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','divider_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'project.c','project_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'rdii.c','validateRdii','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'shape.c','shape_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'snow.c','snow_validateSnowmelt','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'subcatch.c','subcatch_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'table.c','table_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'transect.c','transect_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'climate.c','climate_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateTreeLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateGeneralLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gage.c','gage_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validateAquifer','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','lid_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidProc','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidGroup','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','link_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','conduit_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','pump_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','orifice_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','weir_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','node_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','divider_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'project.c','project_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'rdii.c','validateRdii','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'shape.c','shape_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'snow.c','snow_validateSnowmelt','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'subcatch.c','subcatch_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'table.c','table_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'transect.c','transect_validate','',5);

Please let me know if you need more details.

Update 1: So digging this further I found questions similar to mine.

• sql server freetexttable partial words
• FreeTextTable Ranked Search Not working for word

So if understood those URLs correctly then FreeTextTable doesn't work on one word which is surely not true as I am using one word query on another table and it works just fine.

Answer 1

You are trying to perform a substring match which FreeTextTable does not support. Instead, use ContainsTable with a wildcard.

select * from ContainsTable(SampleTable, ([FName],[IName]), 'clima*')

This search will match these strings:

• "climate.c"
• "climate.c blah blah"
• "blah blah climate.c"
• "blah blah climate.c blah blah"
• "clima"
• "blah blah clima"

But it won't match:

• "xxclimate"

FYI FreeTextTable is for natural language type searches -- synonyms, singular/plural forms, conjugations are matched and only 1 term in the search is required to match. ContainsTable matches the exact words (no synonyms, etc.), boolean operators are allowed (AND, OR, NOT), wildcards (*, ?) are supported, and you can perform proximity searches (word1 within N words of word2).