A hard journey towards full-text indexing of SQL Server

 

Write this articleArticleIt was 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. 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 perform the following operations: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 and full-text filter backgroundProgramWhen the host (fdhost) process is communicating, the error 0x80070218 is encountered. 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 modifying the registry. 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

Text with more than 300 KB (more than 150 KB)NtextColumn Unicode text)TextWhen you run a full-text index containing query on a column that is enabled for full-text, you may receive a message, "0 rows affected", even if valid results exist.

Cause

Microsoft Search Service (mssearch.exe) maintains the character-based data type for full-text search of SQL Server 7.0, as shown inCharacter,Varchar,TextAndNtext.TextYou can have more than 300 kb of data based on your defined columns (NtextCan contain more than 150 kb Unicode text ). When full-text index is filled, the MSSEARCH service starts fromTextRetrieve all non-repeated words in the column and store them in the virtual memory. It merges 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 \ <full_text_catalog_name> \ maxbytesperfile

Example: full_text_catalog_name = sql0000500005
The default value of this registry item is 307200 (300 KB ). Therefore, the MSSEARCH service assumes that the 300 kb text is important and will be ignored when filling the full-text index.TextThe remaining data of the column.
Therefore, when you run the full-text contains clause query to search, the 300 kb text will appear inTextThe words in the column cannot 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:

1. InStartClickRun
2. TypeRegeditAnd then clickOK
3. Find the following items in the Registry Editor:

Hkey_local_computer \ Software \ Microsoft \ Search \ 1.0 \ indexer \ sqlserver \ full_text_catalog_name \

(For example:
Full_text_catalog_name = sql0000500005 ).

1. ClickMaxbytesperfile.
2. InEditClickModifyAnd then specifyNumerical data.
3. Exit Registry Editor.
4. Regenerate the full-text directory.

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 not
Return valid results

Article
ID: 241113-view products that
This article applies.

This
Article was previusly published under q241113

Notice

Important: This article contains information about modifying the registry.
Before you modify the registry, make sure to back it up and make sure that you
Understand how to restore the registry if a problem occurs. For information
About how to back up, restore, and edit the registry, click the following
Article number to view the article in the Microsoft Knowledge Base:

256986

(Http://kbalertz.com/Feedback.aspx? Kbnumber = 256986/en-US/
)

Description
Of the Microsoft Windows Registry

Expand All | collapse
All

Symptoms

When you
Run a full-text contains query on a full-text enabledTextColumn that has more than 300 kb
Text (more than 150 kb of Unicode text forNtextColumn
), You may receive a "0 rows affected" message even though valid results
Are present.

Back to the top | give feedback

Cause

The
Microsoft Search Service (mssearch.exe) maintains the full-text catalogs
SQL Server 7.0 full-text search to search character-based data types suchChar,Varchar,TextAndNtext.
Columns that you defineTextCan have more than 300 kb of data (NtextCan have more than 150 kb of Unicode
Text). When the full-text index is populated, MSSEARCH service Retrieves all
The distinct words fromTextColumn and stores them in the virtual
Memory before it merges them into the master index. The MSSEARCH Property
Prevents the MSSEARCH service from consuming all the virtual memory.
MSSEARCH property is controlled by the following registry and its value:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Search \ 1.0 \ indexer \ sqlserver \
<Full_text_catalog_name> \ maxbytesperfile

For
Example: full_text_catalog_name = sql0000500005

The default value of this registry key is set to 307200 (300 KB). As a result,
MSSEARCH service assumes 300 kb of text as significant and ignores
Remaining data ofTextColumn while it populates
Full-text index.

Therefore, when you run a full-text contains clause query to search a word that
Is present inTextColumn after 300 kb of text, you may
Not obtain valid results.

Back to the top | give feedback

Workaround

Warning: If you use Registry Editor incorrectly, you may cause serious
Problems that may require you to reinstall your operating system. Microsoft
Cannot guarantee that you can solve problems that result from using Registry
Editor incorrectly. Use Registry Editor at your own risk.

To work around this problem:

1. OnStartMenu, clickRun.
2. Type regedit,
   And then clickOK.
3. Locate
   Following key in Registry Editor:

Hkey_local_computer \ Software \ Microsoft \ Search \ 1.0 \ indexer \ sqlserver \ full_text_catalog_name \

(For example:
Full_text_catalog_name = sql0000500005 ).

4. ClickMaxbytesperfileKey of type REG_DWORD.
5. OnEditMenu, clickModify, And then specifyValue DataOf the registry key based on your
   Requirement.
6. Quit Registry
   Editor.
7. Rebuild
   Full-text catalog.

The purpose of this article is to be used in the future (by the way, an English-Chinese comparison is also used to learn English). When I finish reading the article, I suddenly think of a problem that since there is no indexer folder, can I create one manually? As a result, I create one file, and the final content is as follows:

 

Here, I set the maximum text size to 30 mb.

Then, delete the full-text index. After the new index is created, everything is done. Finally, I see the data I want to retrieve.

However, there is a problem here. Because my database has a small amount of data, it cannot be compared with the like search efficiency. In the end, I can only add data and see a little effect.

I would like to dedicate myself to the four-hour period.

All rights reserved (slight cold rain). Please do not reprint it. If you reprint it, please indicate the source.

Saturday, June 15, 2013