Why doesn't FTS match any records?
Posted on 2011-05-13
I created an indexed view with a few fields that i created a full text search based on these fields.
here is my view:
alter VIEW vFullTextSearch
SELECT M.Id as MessageId, M.Subject , M.Body,MR.EmailAddress, MR.FriendlyName,
M.Subject + ' ' + M.Body + ' ' + MR.EmailAddress + ' ' + MR.FriendlyName as FTS
FROM DBO.MESSAGE M
INNER JOIN DBO.MessageRecipient MR ON M.Id = MR.MessageId AND MR.RecipientTypeId=1
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1_vFTS
ON vFullTextSearch (MessageId);
I indexed all the fields of the table.
I am using this SQL to find results
FROM Message AS M
INNER JOIN FREETEXTTABLE(vFullTextSearch, *, 'Doe') AS KEY_TBL
ON M.Id = KEY_TBL.[KEY];
but with "John Doe" being the [vFullTextSearch.FriendlyName] value in all the records of my view why am i not gettign results when searching all the fields for 'doe' ?