Search code examples
sqlfull-text-searchfulltext-index

Fulltext search not yielding results

I've started working with Fulltext indexing, and I've ran into a problem that I can't find a solution for.

Ive created a catalog with 

create FULLTEXT CATALOG [ClaimDbCatalog] AS DEFAULT

Then my table looks like ...

create table Claim( 
Id int identity(1,1) not null ,
DateTimeCreated dateTime not null default getDate(),

ScriptNumber varchar(20) not null,
IsResolved bit not null default 0,
ResolvedDateTime datetime,
PracticeId int not null references dbo.Practice(Id),
CreatedById int not null references dbo.SystemUser(Id)

CONSTRAINT [PK_Claim_Id] PRIMARY KEY CLUSTERED  ([Id] ASC));

Created my index with :

create fulltext index idxClaimonIdFulltext Claim(ScriptNumber) KEY INDEX [PK_Claim_Id] ON ClaimDbCatalog

Then, looking at my test data .. enter image description here Finally, I attempt the fulltext search with 

SELECT * from     CONTAINSTABLE([dbo].[Claim], Scriptnumber, 'PR1234567890')

But this yields no results. I've tried using part of the text, but still no results.

What am I doing wrong?

Solution

  • The issue was due to me doing my tests in a TRAN. The moment I committed the data, the fulltext kicked in and worked.