Topics in this Tutorial
- Full-text searching
- Creating a full-text Catalog
- Populating a catalog
- Querying data with full-text search functions
Full Text Searching
Indexing large text fields externally
One of the new features introduced in Microsoft SQL Server 7.0 was the ability
to use the Internet Information Server's feature of Full-Text Searching. This
uses the MSSearch service to query text data in more sophisticated ways than is
offered by the WHERE clause LIKE. It is also much faster on large tables and
large text fields.
Microsoft SQL Server 2000 Full-text search now includes change tracking
and image filtering.
Change tracking
This maintains a log of all changes to the full-text indexed data. You can
update the full-text index with these changes by flushing the log manually, on a
schedule, or as they occur, using the background update index option.
Image filtering
Image filtering allows you to index and query documents stored in image columns.
The user provides the document type in a column that contains the file name
extension that the document would have had if it were stored as a file in the
file system. Using this information, full-text search is able to load the
appropriate document filter to extract textual information for indexing. For
more information, see Microsoft Search Service.
Create a Full-Text Catalog
Full-Text Catalogs
The Full-Text search capability in SQL Server actually uses external indexes.
This is because Text, nText and image data types cannot be indexed in SQL
Server, and even large Varchar, Char, nVarchar or nChar text fields would be
poor candidates for standard clustered/nonclustered indexes.
Remember too that queries such as this:
Select * from countries
Where notes like '%beach%'
... would never invoke the use of an index in any case.
The full-text search system uses an external index of keywords and phrases that
are linked back to the primary key of the record containing the data. The basic
concept is the same as a web search engine. 'Noise' words such as 'the', 'a',
'or' are ignored. You can choose from several popular languages.
This diagram, taken from the online books, illustrates the idea very clearly:
The Full-Text Indexing Wizard
Once Full-text indexing has been installed you can create a catalog for any
database you like with textual data.
This has a wizard interface and takes you through the catalog building in
several easy steps.
It is invoked from the Tools menu.
- In Enterprise Manager select the database on which you want to
build the catalog
- From the Tools menu, select Full-Text Indexing
- The wizard loads in its usual glitzy way
