SQL full-text index entry and examples

Full-text indexes provide effective support for complex word search in string data. Full-text indexes store information about important words and their locations in specific columns. Full-text query uses this information to quickly search for rows containing a specific word or a group of words.
Full-text indexes are included in the full-text directory. Each database tutorial can contain one or more full-text directories. A directory cannot belong to multiple databases, and each directory can contain full-text indexes of one or more tables. A table can have only one full-text index. Therefore, each table with a full-text index belongs to only one full-text directory.
Full-text directories and indexes are not stored in the databases to which they belong. Directories and indexes are managed separately by the Microsoft search service.
Full-text indexes must be defined on the base table, but not on The View, system table, or temporary table.
     
According to the above description, we can make such a metaphor. You may have seen archive cabinets. The Archive cabinets are classified and registered on the archive index card. This archive cabinet is like a full-text index, using these file index cards, you can quickly locate the location of the file you want to search. If you do not create these index cards, if the number of files is small, it is difficult to find the desired file when the number of files is large. This is similar to the situation where LIKE is used.
     
Differences between full-text indexes and common indexes:
General SQL index full-text index
Storage is controlled by the database where they are located and stored in the file system, but managed by the database
Each table can have several common indexes. Each table can have only one full-text index.
When data is inserted, updated, or deleted as its basic data, it is automatically updated to add the data to the full-text index. The full-text index can be requested by scheduling or specific requests, it can also automatically occur when new data is added.
Groups are not grouped into one or more full-text directories in the same database.
Use the SQL Server Enterprise Manager, wizard, or Transact-SQL statements to create, manage, and remove

2. How to use
Example: see full-text index service using SQL Server2000
The above article makes it clear that some typical SQL statements are listed here:
(For details, you can query contains in SQL Server2000 online)
Returns the description of all categories containing the string "sea" or "bread.
Use Northwind
Select * from categories
Where contains (description, '"sea *" or "bread *"')

(For detailed descriptions, you can query freetext in SQL Server2000 online)
The search product description contains all product categories related to bread, candy, dry, and meat, such as breads, candies, dried, and meats.
USE Northwind
GO
SELECT CategoryName
FROM Categories
Where freetext (Description, 'sweetest candy bread and dry meat ')
GO

3. Suggestions
A. Carefully consider how to maintain the full-text index
[Excerpt from SQL Server2000 online slave book]
There are three ways to maintain full-text indexes:

Complete reconstruction
Scan all rows again. Completely re-create the full-text index. You can perform full reconstruction immediately or by scheduling through the SQL Server proxy.

Timestamp-based incremental reconstruction
Rescan the rows that have been changed since the last full or incremental reconstruction. In this case, a timestamp column is required in the table. Changes that do not update the timestamp (such as WRITETEXT and UPDATETEXT) cannot be detected. You can perform incremental reconstruction immediately or by scheduling.

Change Tracking
Maintains a list of all changes to the index data. Changes made with WRITETEXT and UPDATETEXT cannot be detected. You can use these changes to update the full-text index immediately, or by scheduling, or use the background update index option to update the index once the change occurs.

The method used depends on many factors, such as CPU and available memory, the number and speed of data changes, the size of available disk space, and the importance of the current full-text index. The following suggestions can be used as a reference for selecting maintenance methods.

When the CPU and memory are not faulty, the value of the latest index is very high, and the instant propagation speed can keep up with the change speed, use the change tracking with the background update index option.

When the CPU and memory can be used during the scheduling time, the disk space used to store the changes is large enough, and the changes between the scheduling time are not large enough to make the propagation time longer than the time required for full reconstruction, use change tracking with scheduled propagation.

If changes or additions to most records occur immediately, use full reconstruction. If most of the records are changed during the extended period, consider using the change tracking with scheduling or background update index.

If you change a large number of documents each time (not as a high percentage), you can use incremental reconstruction. If a large number of record changes occur during the extended period, use the change tracking with scheduling or background index updates.

An example of full-text index of the SQL SERVER database, using the pubs database as an example.
The following describes how to create a full-text index using the system stored procedure:
1) start the full-text processing function of the database (sp_fulltext_database)
2) create a full-text Directory (sp_fulltext_catalog)
3) register the table in the full-text directory that requires full-text indexing (sp_fulltext_table)
4) name of the column that requires full-text indexing in the table (sp_fulltext_column)
5) create a full-text index for the table (sp_fulltext_table)
6) fill in the full-text Directory (sp_fulltext_catalog)
--------- ********* Example ********-------------
Create a full-text index for the title and notes columns of the pubs database, and then use the index to query the name of the book containing the datebase or computer string in the title column or notes column:
Before that, you need to install the Microsoft Search service and start the full-text Search service for SQL server.
User pubs -- open the database
Go
-- Check whether the database pubs supports full-text indexing. If not
-- Use sp_fulltext_database to enable this function.
If (select databaseproperty ('pubs', 'isfulltextenabled ') = 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 title table
Execute sp_fulltext_table 'title', 'create', '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
-- Activate: Enables full-text retrieval of a table, that is, registering the table in the full-text directory.
Execute sp_fulltext_table 'title', '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
-- After the full-text directory is filled, you can use full-text directory retrieval.
Select title
Form
Where CONTAINS (title, 'database ')
Or CONTAINS (title, 'computer ')
Or CONTAINS (notes, 'database ')
Or CONTAINS (notes, 'database ')

'-------------- The following describes the full-text operating system stored procedures.
Procedure name: sp_fulltext_service
Execution permission: serveradmin or System Administrator
Usage: set full-text search attributes
Procedure name: sp_fulltext_catalog
Execution permission: db_owner and higher role members
For use: create and delete a full-text directory, and start or stop the index operation of a full-text directory.
Procedure name: sp_fulltext_database
Execution permission: Member of db_owner role
For use: initialize the full-text index or delete all full-text directories in the database
Procedure name: sp_fulltext_table
Execution permission: db_ddladnmin or db_owner role member
For use: identify a table as a full-text index table or a non-full-text index table.
Procedure name: sp_fulltext_column
Execution permission: Member of the db_ddladnmin role
Usage: indicates the columns in a full-text index table. If the full-text index is left or not