Creating Indexed Views For Full Text Search

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Creating Indexed Views For Full Text Search

Author

Topic

staplebottom
Starting Member

29 Posts

Posted - 2007-11-30 : 17:14:53

Hi,

I have problem that Im sure others must have had before so I am looking for advice on the best way to solve it. I have a table of text information tbl_base which is related to another table containing tags, which can contain several rows for each row in base.

I want to create an indexed view of the data for full text search. I would like to select all the tags related to a particular row in the tbl_base together into a string and join it to the end of the tbl_base table so they can be indexed along with the tbl_base data for full text indexing. Ive tried several methods but I am never able to make an index on my view because it say I cant use COALESCE, or Cursors.

Sample data here :

tbl_base

id | Text
------------------------
1 | BLah blah
2 | Dum de dum
3 | HAr HAr

tbl_base_tags

tagID | base_id | TagText
--------------------------
1 | 1 | first
2 | 1 | second
3 | 1 | third
4 | 2 | fourth

The view I want to end up with will look like this :

vw_tables

base_id | Text | Tags
-------------------------------------
1 | Blah blah | first second third
2 | dum de dum | fourth

How can I achieve this? Do I have to index all the tables seperately?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-30 : 20:31:29

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93454

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources