Learning full-text indexing of SQL Server)

when querying a product on a product introduction website, the product's introductory text may be long. If you use like to perform fuzzy search on the product introduction field, performance is definitely a problem. How can this problem be solved? The first idea is to use full-text indexes. So what is full-text index, how should it be applied, and what should be paid attention to during the application process? This post is used as a note for learning full-text retrieval.
1. What is it
[Extract from SQL Server2000 online slave book]
full-text indexing provides 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 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 rather than on the view, system table, or temporary table.
based on the above description, this metaphor can be used. 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:

common SQL indexes	full-text index
storage is controlled by the database where they are stored	stored in the file system, but managed through 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 the base, it is automatically updated	adding data to a full-text index is called filling. A full-text index can be requested by scheduling or specific requests, it can also happen automatically when new data is added
not grouped	group one or more full-text directories in the same database
use the SQL Server Enterprise Manager, wizard, or transact-SQL statements to create and remove them	use the SQL Server Enterprise Manager, wizard, or stored procedure to create, manage, and remove data

2. How to Use
Example: see full-text index service using SQL Server2000
AboveArticleIt is 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. To do this, you need to haveTimestampColumn. 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.

However, even after you select the job type, you should make appropriate planning for the scheduling of full-text indexes. Because data changes in a table affect the full-text index content, it is not suitable for full-text indexes for tables that frequently update data. At the same time, you can set the time for filling the full-text index during scheduling when the system is relatively idle, and the possible time for filling should be taken into account. For example, you can set the filling time to 0: 00 every night. At this time, you should be relatively idle (this idea is a bit suspect, but it should be similar in general ).
In addition, a filling experiment should be performed to simulate the possible data volume of the customer to estimate the time length of the index filling.