The FTS feature allows you to retrieve specific text data from columns in a
database. It does this by first indexing those columns, and then providing you
the ability to query the indexes that were created. You may now be asking, Why
can't I use the LIKE T-SQL predicate to do this? Well, the FTS feature
gives you the following advantages over the LIKE predicate:
• With FTS you can perform a linguistic search of character data.
A linguistic search can operate on words and phrases unlike a LIKE predicate.
• With FTS you can weigh how well the results that are returned
rank in comparison to the original search condition. This is not possible
with a LIKE predicate because it only returns the results that contain the
query term.
• With FTS you can remove noise words (such as "and,"
"or," etc.) from the search criteria. This makes the search
more effective, and it is not possible with the LIKE predicate.
• With FTS you can conduct language-specific searches. This
is not possible with the LIKE predicate.
• FTS is more effective at searching large tables (greater than
1 million rows) because it uses a precomposed index. In comparison, a
LIKE predicate must search for the string through the whole table at query
time.
• FTS can be used to index and query certain types of data stored
in image columns. It uses one of several supported filters to interpret
the data and extract the text data for indexing and querying. SQL Server provides
filters for the .doc, .xls, .ppt, .txt, and .htm file extensions. You can
create custom filters for full-text indexing of additional file types. For
more information about creating custom filters, search on "custom filters"
in the Platform SDK section of the MSDN Library at the Microsoft Web site.
There are third-party products that provide the functionality above, but why
use those products when you get the same thing as part of the SQL Server package?
FTS uses the Microsoft Search service as its engine, so the Microsoft Search
service will also be installed when FTS is installed. In SQL Server 2000, FTS
is installed by default with a typical installation of SQL Server 2000 Standard
and Enterprise editions. In SQL Server 7.0, FTS has to be added as part of a
custom installation of the Standard or Enterprise editions.
There are two major components that exist in FTS:
• Indexing Component. This is responsible for the initial
population of the full-text index and the subsequent update of this index
when the data in the full-text indexed tables is modified.
• Query Component. This accepts a full-text predicate from
SQL Server, transforms the predicate into a command tree, and sends the command
tree to the Microsoft Search service.
Commonly Used Full-Text Search Terms
A full-text index stores all the full-text words and their locations for a
given table. A full-text index must be defined on a base table; it cannot be
defined on a view table, a system table, or a temporary table. A full-text index
definition includes
• A column that uniquely identifies each row in the table (primary
or candidate key) and does not allow NULLs.
• One or more character string columns covered by the index.
Full-text indexes and regular SQL indexes differ in the following ways:
|
|
|
| • Stored under the control of the database in which
they are defined. |
• Stored in the file system, but administered through
the database. |
| • Several regular indexes per table are allowed. |
• Only one full-text index per table is allowed. |
| • Updated automatically when the data upon which they
are based is inserted, updated, or deleted. |
• Addition of data to full-text indexes, called a population,
can be requested through either a schedule or a specific request,
or can occur automatically with the addition of new data. |
| • Not grouped. |
• Grouped within the same database into one or more
full-text catalogs. |
| • Created and dropped using SQL Server Enterprise Manager,
wizards, or Transact-SQL statements. |
• Created, managed, and dropped using SQL Server Enterprise
Manager, wizards, or stored procedures.
|
Full-text indexes are contained in full-text catalogs. A catalog cannot belong
to multiple databases and each catalog can contain full-text indexes for one
or more tables.
A population is the addition of data from the full-text indexed table to the
full-text index. There are three types of populations:
• Full Population. If a full population is requested for a
full-text catalog, index entries are built for all the rows in all the tables
covered by the catalog. If a full population is requested for a table, index
entries are built for all the rows in that table. A full population typically
occurs when a catalog or index is first populated; the indexes can then be
maintained using change tracking or incremental populations.
• Change Tracking Population. Maintains a record of the rows
that have been modified in a system table and propagates the changes to the
full-text index. When using change tracking, you also specify when the changes
are taken from the history table and populated in the full-text index. (This
does not exist in SQL Server 7.0.)
• Incremental Population. Only adjusts index entries for rows
added, deleted, or modified after the last population. This feature requires
that the indexed table have a column of the timestamp data type. If the table
does not have a timestamp column, only full or change tracking populations
can be performed. Requests for incremental populations on tables without timestamp
columns result in a full population operation.
Implementing a Full-Text Search
Implementing FTS in a database involves the following tasks:
1. Identify the tables and columns that are to be registered for FTS.
2. Index the data in the registered columns and populate full-text indexes
with the nonextraneous words.
3. Issue queries against the registered columns for populated full-text indexes.
4. Ensure that subsequent changes to the data in registered columns get propagated
to the index, thus keeping the full-text index synchronized with the data.
Tasks 1, 2, and 4 are accomplished using the graphical tools and wizards, available
through SQL Server Enterprise Manager or built-in stored procedures. The following
steps show how tasks 1 and 2 can be accomplished with the built-in stored procedures.
1. Enable the database for full-text indexing:
| sp_fulltext_database [@action =] 'action' |
In the following example, the pubs database is
enabled for full-text indexing:
use pubs
sp_fulltext_database 'enable'
go |
2. Create the full-text catalog.
In the following example, a catalog named pubs is
created:
use pubs
sp_fulltext_catalog 'pubs', 'create'
go |
The preceding catalog is created in the default folder. If you do not want
SQL Server to create the catalog in the default folder, you can specify the
root folder by using this code:
sp_fulltext_catalog 'pubs', 'create', 'x:\Full-text Catalogs\'
go |
3. Register the table for full-text indexing.
In the following example, the authors table is registered
for full-text indexing:
use pubs
sp_fulltext_table 'authors', 'create', 'pubs', 'UPKCL_auidind'
go |
4. Specify the column for full-text indexing:
sp_fulltext_column [ @tabname = ] 'qualified_table_name'
,
[ @colname = ] 'column_name' ,
[ @action = ] 'action'
[ , [ @language = ] 'language' ]
[ , [ @type_colname = ] 'type_column_name' ] |
In the following example, the au_fname column is
specified for full-text indexing:
use pubs
sp_fulltext_column 'authors', 'au_fname', 'add'
go |
If you have to specify multiple columns, then you must run the preceding
stored procedure multiple times.
5. Activate the full-text index.
In the following example, the full-text index on the authors
table is activated:
use pubs
sp_fulltext_table 'authors', 'activate'
go |
6. Start a full population.
To start a full population on a catalog basis, use the following stored procedure:
sp_fulltext_catalog [ @ftcat = ] 'fulltext_catalog_name'
,
[ @action = ] 'action'
[ , [ @path = ] 'root_directory' ]
|
The following example starts a full population on the pubs
full-text catalog:
use pubs
sp_fulltext_catalog 'pubs', 'start_full'
|
To start a full population on a table basis, use the following stored procedure:
sp_fulltext_table [ @tabname = ] 'qualified_table_name'
, [ @action = ] 'action'
[ , [ @ftcat = ] 'fulltext_catalog_name'
, [ @keyname = ] 'unique_index_name' ] |
The following example starts a full population of the full-text index that
is associated with the authors table:
use pubs
sp_fulltext_table 'authors', 'start_full'
go
|
© 2003 Microsoft