Take the 2-minute tour ×
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.

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
WITH SCHEMABINDING
AS
    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 
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1_vFTS
    ON vFullTextSearch (MessageId);
GO

I indexed all the fields of the table.

I am using this SQL to find results

SELECT M.*
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' ?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

According to MSDN...http://msdn.microsoft.com/en-us/library/ms177652.aspx

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

Union

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: technet.microsoft.com/en-us/library/cc917695.aspx 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

 
discard

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.