A hard journey towards full-text SQL server indexing

Entering SQLServer full-text index, the difficult process of writing this article was not planned, and I have never touched the full-text index before. Occasionally preparing lessons. Suddenly inspired. You decided to record the process of creating and using full-text indexes for future use. As for the concept of full-text index, there are a lot of online content, so I will not go into details here. Direct correction

I entered the full-text index of SQL Server, but I was not planning to write this article. I have never touched the full-text index before. Occasionally preparing lessons. Suddenly inspired. You decided to record the process of creating and using full-text indexes for future use. As for the concept of full-text index, there are a lot of online content, so I will not go into details here. Direct correction

A hard journey towards full-text indexing of SQL Server

This article is not planned and has never been used to full-text indexing. Occasionally preparing lessons. Suddenly inspired. You decided to record the process of creating and using full-text indexes for future use.

As for the concept of full-text index, there are a lot of online content, so I will not go into details here. Directly go to the topic.

To create a full-text index, follow these steps:

1: Right-click the database, select Properties, select a file, and select "use full-text index"

This option can be selected by default.

2: Right-click the table and choose full-text index> define full-text index.

3: click Next. If the table does not have a unique index, it will be shown.

4: select a table column and the delimiter language.

5: click Next. The options here should be noted. If you do not want to update the full-text index when the table or view is changed, do not track the changes; in this way, you can choose whether to enable full filling during index creation.

6: Click Next to create the directory for storing the index. On the Hong Kong server, the full-text index file is saved to the hard disk as a file.

7: Then you can set Automatic Filling, manually track changes, and set the plan.

Although the above steps are simple, I encountered N problems during creation.

Problem description:

01. When I right-click a table, the full-text index menu item is gray and unavailable.

For example

You need to execute the following command to enable this menu item through data query.

EXEC sp_fulltext_database 'enable'

In this way, the full-text index becomes available.

If you want to reset the full-text index, you can run the following code:

EXEC sp_fulltext_database 'disable'

02. You must enable the full-text search service when using full-text indexing for retrieval.

By default, this service is disabled.

03. When I create a full-text index and use the following statement to index keywords

Select content from info infoinfo

Where contains (content, 'but in order to achieve optimal performance, we recommend that you use primary key constraints or unique constraints. ')

The following prompt is displayed:

"SQL Server encounters an error of 0x80070218 when communicating with the full-text filter background program host (FDHost) process. Make sure that the FDHost process is running. To restart the FDHost process, run the sp_fulltext_service 'Restart _ all_fdhosts' command or restart the SQL Server instance. "

After repeated exploration, I found that the full-text service running account needs to be changed in the SQL Configuration tool. The default "system settings" --> "Local Account" is available!

However, after I changed the settings, I still couldn't make a smile, because although there were no errors next, but I couldn't retrieve the results I needed, but I searched through like, And there was indeed data.

Select content from infoinfo

Where content like '%, but in order to achieve optimal performance, we recommend that you use primary key constraints or unique constraints. %'

At this time, I almost collapsed. No way. Continue searching for materials.

Finally, I found this article on the Microsoft official website.

It details the cause of the problem and the corresponding solution.

Note:

Important: This article contains information about registry modification. Before modifying the registry, you must back up the registry and ensure that you understand how to restore the registry. For how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Microsoft Windows registry description

Symptom

If you run a full-text index on a full-text enabled column of a text with more than 300 KB (UNICODE text with more than 150 KB ntext columns) that contains a query, you may receive a message in US space, "0 rows affected", even if valid results exist.

Cause

Microsoft Search Service (MSSearch.exe) maintains the full-text directory of characters, such as characters, varchar, text, and ntext, to be searched by SQL Server 7.0 full-text search. The text can contain more than 300 KB of data based on your defined columns (ntext can contain more than 150 KB of UNICODE text ). When a full-text index is filled, the MSSearch service Retrieves all non-duplicate words from the text column and stores them in the virtual memory, which combines them before the primary index. The MSSearch attribute prevents the virtual memory occupied by the MSSearch service. The MSSearch attribute is controlled by the following registry and its value:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Search \ 1.0 \ Indexer \ SQLServer \ \ MaxBytesPerFile

Example: Full_Text_Catalog_Name = SQL0000500005
The default value of this registry item is 307200 (300 KB ). Therefore, the MSSearch service assumes that 300 KB of text is important and full-text indexing will ignore the remaining data in the text column.
Therefore, when you run the full-text CONTAINS clause query to search, the 300 KB text will appear in the text column, and it is impossible to obtain valid results.

Alternative Method

Warning improper use of the Registry Editor may cause serious problems and you may need to reinstall the operating system. Microsoft cannot guarantee that you can solve problems caused by improper use of the Registry Editor. You are at your own risk to use the Registry Editor.
To solve this problem:

Hkey_local_computer \ SOFTWARE \ Microsoft \ Search \ 1.0 \ Indexer \ SQLServer \ Full_Text_Catalog_Name \

(For example, Full_Text_Catalog_Name = SQL0000500005 ).

But when I followed this document, I encountered a problem again, that is, after steps 3rd to 1.0, there was no Indexer folder, and I was dizzy, I had to dig my head again. As a result, there were very few materials on the Internet, but an identical English article was found.

PRB: A Full-Text Search Query on TEXT or NTEXT Column Does NotReturn Valid Results

ArticleID: 241113-View products thatthis article applies.

Thisarticle was previusly published under Q241113

Notice