Learning full-text indexing of SQL Server

When you query a product on a product introduction website, because the introductory text of the product may be very long, if you use like for a fuzzy query of 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 the note for learning full-text retrieval.

1. What is

[Excerpt from SQL Server2000 online slave book]

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 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 the archive cabinet. The Archive cabinet registers various archives on the archive index card by category. This archive cabinet is like a full-text index.
Some file index cards can quickly locate the location of the file you want to find. If you do not create these index cards, if the number of files is not large enough, once the number of files is large, it is obviously difficult to find the desired file.
Similar to the case where like is used.



Differences between full-text indexes and common indexes:

Common SQL Index	Full-text index
Storage is controlled by the database where they are located	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 its basic data, 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, or it can automatically occur when new data is added.
Do not group	Groups one or more full-text directories in the same database.
Use the SQL Server Enterprise Manager, wizard, or transact-SQL statement to create and remove	Use the SQL Server Enterprise Manager, wizard, or stored procedure 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. To do this, you need to haveTimestamp
  Column. 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 changes to data in a table affect the full-text index content, tables that frequently update data are not
It is too suitable for full-text indexing. 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 every night.
0: 00, this time should be relatively idle (this idea is somewhat suspected
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.



Ah ~~ Full-text search involves a wide range of fields. Sort these items first ~!