Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
        INNER JOIN DBO.MessageRecipient MR ON M.Id = MR.MessageId AND MR.RecipientTypeId=1 

--Create an index on the view.
    ON vFullTextSearch (MessageId);

I indexed all the fields of the table.

I am using this SQL to find results

FROM Message AS M
        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' ?

share|improve this question
up vote 4 down vote accepted

According to MSDN...

table Is the name of the table that has been marked for full-text querying. table or view can be a one-, two-, or three-part database object name. When querying a view, only one full-text indexed base table can be involved. table cannot specify a server name and cannot be used in queries against linked servers.

Since your view has full text indexes on more than one table you will not be allowed to do this.

You could possibly use a union to check both tables in your view.

SELECT {your select columns}
FROM Message AS M
    INNER JOIN FREETEXTTABLE(dbo.Message, {your message columns}, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY]
    INNER JOIN MessageRecipient MR on M.Id = MR.MessageId AND MR.RecipientTypeId=1


SELECT {your select columns}
FROM Message AS M
    INNER JOIN MessageRecipients MR on  M.Id = MR.MessageId AND MR.RecipientTypeId=1
    INNER JOIN FREETEXTTABLE(MR.MessageRecipients, {your MessageRecipients columns}, 'Doe') AS KEY_TBL
        ON MR.Id = KEY_TBL.[KEY];

I don't have access to a SQL installation right now, but let me know if it works.

share|improve this answer
well why would i not be able to just index the last field in my view [FTS] that is an amalgam of all the fields i need to search? – kacalapy May 13 '11 at 13:02
As a note from another msdn article: So the MSFTESQL engine creates indexes on each keyword with a link to the primary key of the particular record that the keyword refers to. So if you try to link KEY_TBL.KEY to Message.ID, then the FreeTextTable function can return keys from both Message and MessageRecipients. SQL won't know which key to map to. So if you are searching for text in the subject of the message and the To list of message recipients, this is why you can't combine the fields. – DBADarron May 16 '11 at 22:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.