Full-Text search and database view

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Denis Blondeau - 23 Feb 2005 19:31 GMT

My understanding from full-text search (with SQL 2000) is that you can only
search a table and not a view; i.e. you cannot create a full-text index for
a view.

1) Is there a way to full-text search a view?

2) If not, what could I be using the search data in the database using a
view which is better (faster) than a "LIKE "?

Thanks...

Denis.

Reply to this Message

Hilary Cotter - 23 Feb 2005 21:00 GMT

In SQL 2005 you can full text index and search views.

Currently in SQL 2000 you can't full text index views, so you will have to
materialize the view as a table, i.e. create a table which has the same
columns as a view and use replication or triggers to keep this table in sync
with the base table. Then FTI this table.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> My understanding from full-text search (with SQL 2000) is that you can only
> search a table and not a view; i.e. you cannot create a full-text index for
[quoted text clipped - 8 lines]
>
> Denis.

Reply to this Message

John Kane - 24 Feb 2005 03:00 GMT

Denis,
Yes, your understanding is correct for SQL Server 2000.

1) Is there a way to full-text search a view?
A. You cannot FT Index the "contents" of a view, however, you can include
SQL FTS statements such as CONTAINS or FREETEXT in a view.

2) If not, what could I be using the search data in the database using a
view which is better (faster) than a "LIKE "?
A. Depending upon your requirements (and more info on this would be
helpful), and assuming that you want to use FTS to search either multiple
columns or multiple tables & column in one SQL FTS statement, you may want
to checkout "SQL Server FTS across multiple tables or columns" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry

If you have further questions on how to enhance SQL FTS, feel free to leave
a comment!
Thanks,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> My understanding from full-text search (with SQL 2000) is that you can only
> search a table and not a view; i.e. you cannot create a full-text index for
[quoted text clipped - 8 lines]
>
> Denis.

Reply to this Message

Denis Blondeau - 28 Feb 2005 15:11 GMT

Thanks for your help, John (and Hilary)!

> Denis,
> Yes, your understanding is correct for SQL Server 2000.
[quoted text clipped - 9 lines]
> columns or multiple tables & column in one SQL FTS statement, you may want
> to checkout "SQL Server FTS across multiple tables or columns" at

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry

> If you have further questions on how to enhance SQL FTS, feel free to leave
> a comment!
[quoted text clipped - 14 lines]
> >
> > Denis.

Reply to this Message