Hi Jim,
There is a lot of information in SQL Server 2000 Books Online. I think
full-text query generally provides more flexibility.
The Microsoft? SQL Server? 2000 full-text query component supports
sophisticated searches on character string columns.
This capability is implemented by the Microsoft Search service, which has
two roles:
Indexing support
Implements the full-text catalogs and indexes defined for a database.
Accepts definitions of full-text catalogs, and the tables and columns
making up the indexes in each catalog. Implements requests to populate the
full-text indexes.
Querying support
Processes full-text search queries. Determines which entries in the index
meet the full-text selection criteria. For each entry that meet the
selection criteria, it returns the identity of the row plus a ranking value
to the MSSQLServer service, where this information is used to construct the
query result set. The types of queries supported include searching for:
Words or phrases.
Words in close proximity to each other.
Inflectional forms of verbs and nouns.
The full-text engine runs as a service named Microsoft Search on Microsoft
Windows NT? Server, Windows NT Advanced Server, Windows? 2000 Server, or
Windows 2000 Advanced Server. It is installed when the Full-Text Search
feature is selected during custom installation. The Microsoft Search
service itself is not installed during an installation of SQL Server
Desktop Engine. While this means that the Microsoft Search service is not
installed on Microsoft Windows 95, Windows 98, Windows NT Workstation, or
Windows 2000 Professional clients, these clients can make use of the
service when connected to an instance of SQL Server 2000 Standard Edition,
SQL Server 2000 Developer Edition, or SQL Server 2000 Enterprise Edition.
The full-text catalogs and indexes are not stored in a SQL Server database.
They are stored in separate files managed by the Microsoft Search service.
The full-text catalog files are not recovered during a SQL Server recovery.
They also cannot be backed up and restored using the Transact-SQL BACKUP
and RESTORE statements. The full-text catalogs must be resynchronized
separately after a recovery or restore operation. The full-text catalog
files are accessible only to the Microsoft Search service and the Windows
NT or Windows 2000 system administrator.
Communications between SQL Server and the Microsoft Search service are made
through a full-text provider.
The full-text catalogs, indexes, and searches supported by the Microsoft
Search service apply only to tables in SQL Server databases. The Windows NT
Indexing Service and Windows 2000 Indexing Service provides similar
functionality against operating system files. Indexing Service includes an
OLE DB Provider for Indexing Service that can be used by OLE DB consumers.
SQL Server applications can access the OLE DB Provider for Indexing Service
through distributed queries. Transact-SQL statements can combine full-text
searches referencing SQL Server tables with textual searches of file data
by using both the full-text SQL constructs with distributed query
references to the OLE DB Provider for Indexing Service. For more
information, see Full-text Querying of File Data.
There is only one Microsoft Search service on any computer running multiple
instances of SQL Server. The single instance of the full-text search engine
manages the full-text indexes for all the instances of SQL Server 2000 and
SQL Server version 7.0 on the computer.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--------------------
| From: jim-watters@rogers.com (Jim)
| Newsgroups: microsoft.public.sqlserver.fulltext
| Subject: When to use Full Text Indexes
| Date: 7 Jul 2003 09:58:47 -0700
| Organization:
http://groups.google.com/ | Lines: 31
| Message-ID: <93f2172.0307070858.6939315b@posting.google.com>
| NNTP-Posting-Host: 207.236.68.195
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1057597127 30023 127.0.0.1 (7 Jul 2003
16:58:47 GMT)
| X-Complaints-To: groups-abuse@google.com
| NNTP-Posting-Date: 7 Jul 2003 16:58:47 GMT
| Path:
cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sn-xit-03!sn-xit-01!sn-
xit-09!supernews.com!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.fulltext:1184
| X-Tomcat-NG: microsoft.public.sqlserver.fulltext
|
| Hello,
|
| We are discussing the requirements of a database application with one
| of them being the ability for users to serch a text field that
| contains a description of a play in a hockey/basketball game.
|
| For example: Wayne Gretzky power play goal was his 50th of the year
|
| My impression is that this would be served best by a full text index.
| An arguement is being made that we can accomplish this by using LIKE
| to handle the string matching in the query.
|
| My questions are:
|
| 1. What are the critieria that would justify a full text index?
| 2. What are the limitations of LIKE based queries?
|
| Is this based on the average number of characters in the text field
| and the expected number of records(rows) added to the table?
|
| The table that will contain the full text index will be adding
| approximately 100000 records per hockey/basketball season and consist
| of three columns a
| foreign key, time and the description which will contain on average 50
| characters. The system will be running on Windows 2000 server with SQL
| Server 2000 Std Edition, unless we decide not to go with Full Text
| functionality then we would consider using MSDE
|
| Thanks for your help in advance.
|
| Jim
|
