Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jgd12345
Starting Member
6 Posts |
 Posted - 2007-09-07 : 16:06:46
|
| 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_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryIDGOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON 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. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-07 : 23:10:17
|
| Looks like it's not related to full text index. As message said, you can't create index on view that contains certain type of data. |
 |
|
|
jgd12345
Starting Member
6 Posts |
Posted - 2007-09-08 : 04:45:24
|
| So is there no way this can be done. Really appreciate if someone could suggest an alternative way of searching for rows in my database across multiple tables and then adding a weighting score against my results. |
|
|
|
|
|
|
|
|
Adding a full text search across multiple tables