Detailed full-text search in SQL

Full-text indexing and full-text retrieval are new functions of SQL server 7.0. It can be used to search for character columns (such as varchar and text columns) in data.
And full-text search and query through the index. Compared with full-text retrieval, SQL server's regular indexes have the following differences:

General Index full-text index
Use create index or constraint definition to create and delete a full-text index Stored Procedure
Delete by deleting or executing the drop index statement

When inserting, modifying, or deleting data, SQL server can only store data through task scheduling or execution
Ability to automatically update the general index content to fill the full-text index

Each table can have multiple regular indexes. Each table can have only one full-text index.
Indexes cannot be grouped into multiple full-text indexes in the same database.
Organize as a full-text directory
Regular indexes are stored in database files. Full-text indexes are stored in the file system.

To support full-text indexing, SQL server 7.0 adds some new stored procedures and transact-SQL statements.
This step is used (the name of the stored procedure called in each step is enclosed in brackets ):

(1) Start the full-text processing function of the database (sp_fulltext_datebase );
(2) create a full-text directory (sp_fulltext_catalog );
(3) register the table (sp_fulltext_table) that requires full-text indexing in the full-text directory );
(4) Name of the full-text search column in the table (sp_fulltext_column)
(5) create a full-text index for the table (sp_fulltext_table );
(6) Fill in the full-text index (sp_fulltext_catalog ).

Example:
Use pubs
Go
Exec sp_fulltext_database 'enable'
-- Create full-text index data elements for the titles table, where "create" is created, "activate" is activated, and "deactivate" is activated to disable the full-text index of the table.
It does not participate in full-text directory filling. drop is delete. In the create parameter, the full-text directory name and index column name are followed.
-- The following statement creates a full-text index data element for the titles table in the pubs database. The full-text directory for storing the data element is FT_pubs, and the unique index used is
UPKCL_titleidind (unique index created by the primary key constraint of the title_id column in the title table)
Sp_fulltext_table titles, 'create', 'ft _ pubs', 'upkcl _ titledind'

-- Activate it
Sp_fulltext_table titles, 'activate'

-- Specify the columns that participate in the full-text index
Sp_fulltext_column 'title', 'title', 'add'
Sp_fulltext_column 'tidles ', 'note', 'add'

The following is a complete example:
-- Start the full-text search service of SQL server before executing the script program, that is, microsoft search.
Use pubs -- open the database
Go
-- Check whether pubs supports full-text indexing. If full-text indexing is not supported, use sp_fulltext_datebase to enable this function.
If (select databaseproperty ('pubs', 'isfulltextenables ') = 0
Execute sp_fulltext_database 'enable'
-- Create the full-text directory FT_pubs
Execute sp_fulltext_catalog 'ft _ pubs ', 'create'
-- Create full-text index data element for the titles table
Execute sp_fulltext_table 'title', 'ft _ pubs', 'upkcl _ titleidind'
-- Set full-text index column name
Execute sp_fulltext_column 'title', 'title', 'add'
Execute sp_fulltext_column 'title', 'note', 'add'
-- Create full-text index
Execute sp_fulltext_table 'ft _ pubs ', 'activate'
-- Fill in the full-text INDEX DIRECTORY
Execute sp_fulltext_catalog 'ft _ pubs ', 'start _ full'
GO
-- Check full-text directory Filling
WHILE FulltextCatalogProperty ("FT_pubs ', 'populatestatus') <> 0
BEGIN
-- If the full-text directory is being filled, wait 30 seconds and check again.
Waitfor delay '0: 0: 30'
END
-- Use full-text directory retrieval after full-text directory Filling

-- Query the name of a book with a database or computer string in the title or notes column
SELECT title
FROM title
Where CONTAINTS (title, 'database ')
Or contains (notes, 'database ')
Or contains (title, 'computer ')
Or contains (notes, 'computer ')