How do I create a fulltext index on a view?

Question

Post reply

How do I create a fulltext index on a view?

Afro Blanca

SSC-Addicted

Points: 499
More actions
October 30, 2007 at 6:01 pm

#183493

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

Afro Blanca SSC-Addicted Points: 499 More actions · Answer 2

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?

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 3

Indexed view is stored in database like normal table so I would not expect any performance problems.

Hi

Michal

Mauve SSChampion Points: 11316 More actions · Answer 4

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.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

David Burrows SSC Guru Points: 65143 More actions · Answer 5

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.