October 30, 2007 at 6:01 pm
Hello all,
I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :
SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.
However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.
So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.
It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :
A unique column must be defined on this table/view.
How do I create a unique column within a view?
Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
October 31, 2007 at 5:07 am
You can only create full-text indexes on tables or on indexed views.
1. Create the view with SCHEMABINDING option.
2. Create a UNIQUE INDEX on this view.
3. Create FULL-TEXT INDEX
--Ramesh
October 31, 2007 at 8:15 am
Thanks for the help - I plan on trying this today. One question - what will performance be like? Would a fulltext query against an indexed view be slower than a fulltext query against a regular table?
November 1, 2007 at 1:53 am
Indexed view is stored in database like normal table so I would not expect any performance problems.
Hi
Michal
November 1, 2007 at 7:10 am
Unlike Oracle, which can perform full-text queries against a view having one or more tables that have full-text indexes, SQL Server is quite dumb in this area.
Independent of the table joining which is a common use for a view, if a view is just created as a 1:1 maping over the table (e.g., table "a" is full-text indexed, view "b" is SELECT * FROM a), SQL Server cannot resolve to the underlying object. It is quite evident that query optimizer only looks at "real" tables.
In my opinion this is quite stupid, as for any number of reasons, you may wish to limit access to rows and/or columns in the table. They way it works today (SQL Server 2005), you are force to grant select access on the entire [base] table. To redundantly store all of this data in an indexed view, would be asinine.
November 1, 2007 at 8:37 am
As a matter of interest why do you not do this
SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, title, @searchString) AS ft1 ON ci.contentItemId = ft1.
INNER JOIN FREETEXTTABLE(Content_Pages, pageText, @searchString) AS ft2 ON cp.contentItemId = ft2.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply