Adding a full text search across multiple tables (with text fields)

Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:

Documents:
- DocumentID (int, PK)
- Title (varchar)
- Content (text)
- CategoryID (int, FK)

Categories:
- CategoryID (int, PK)
- CategoryName (varchar)

I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:

CREATE VIEW vw_Documents
WITH SCHEMABINDING AS
SELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID

GO
CREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndex
ON vw_Documents(DocumentID)

But this gave me the error:

Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.

I tried converting the Content to a varchar(max) within my view but it still didn't like.

Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

Hi jgd12345,

After talking with my colleagues i find a solution:

First you need to change Content datatype from text to varchar(max);

Then set the following options:

SET CONCAT_NULL_YIELDS_NULL ON

|||Cheers, i'll test this out when i get back to the office. Thanks again.