SQL2000 Full-text indexing fully illustrated _mssql

Full-Text Search can retrieve varchar,text,image fields, but a table can only build one Full-text index
SQL Server 2000 introduces the ability to perform full-text searches on these types of data stored in the image column.
If there is no Full-text index, a fuzzy query on characters can only perform a full table scan (or index Scan) on the base table.
Executing a fuzzy query requires a full table scan or an index scan to mean consuming a lot of IO.
If a fuzzy query occurs frequently, it can cause database performance to deteriorate.
For simplicity, this article demonstrates very complete and efficient full-text indexing only on varchar fields
Create and maintain procedures.
1:
In Enterprise Manager, expand the database in which you want to establish a Full-text index, right-click Full-text catalog, and select New full-text catalog
As shown in Figure 1.
You can modify the path to a Full-text catalog, which can be more efficient by placing the Full-text catalog on a separate disk or disk array.
It is not recommended to have any form of fill scheduling for the entire Full-text catalog.

=================== Figure 1 ===================
2:
Next, you add a Full-text index to a specific table.
A Full-text index cannot be built on a view and can only be built on an underlying table that has a unique index (a primary key and a unique index).
Figure 2 below shows how to establish a Full-text index on a table.
The script for building tables and initializing data for the T_fulltext table in the demo is as follows:
CREATE TABLE T_fulltext
(
RID int identity (1,1),
Title varchar (256) NOT NULL,
Writetime datetime Default (GETDATE ())
)
Go
ALTER TABLE T_FULLTEXT ADD constraint Pk_fulltext primary key (RID)
Go
--Initializing data
Insert into T_fulltext (title) VALUES (' 2 dead 3 people died in the water seepage accident of Penglai Hexi Gold Mine, Shandong (21:29) ')
Insert into T_fulltext (title) VALUES (' Thailand will set up Consulate General in Nanning, Guangxi (21:10) ')
Insert into T_fulltext (title) VALUES (' Hebei Anhui and other 12 provinces, such as tax relief policy implementation in place (20:43) ')
Insert into T_fulltext (title) VALUES (' Taiwanese scholar Tuomeng predicts Chen Shui-bian's New Year's Speech (20:04) ')
Insert into T_fulltext (title) VALUES (' Macau chief executive's New Year's Day message to the public greeting the New Year (20:02) ')
Insert into T_fulltext (title) VALUES (' first completed project of South-to-north Water Diversion (Jeziping) water success in the main canal of Shandong (19:48) ')
Insert into T_fulltext (title) VALUES (' PHOTOS: Beijing 110 motorcycle Patrol appearance (18:07) ')
Insert into T_fulltext (title) VALUES (' People in Fujian, Guangxi, the death of the hospital responsible person was treated (17:33) ')
Insert into T_fulltext (title) VALUES (' Beijing large capacity bus operation first day encounter full passenger (17:30) ')
Insert into T_fulltext (title) VALUES (' Beijing Public security system today all stop to check five rings inside the Blasting (17:30) ')
Insert into T_fulltext (title) VALUES (' The study found that the dragon gene has a lethal effect on the avian influenza virus (17:27) ')
Insert into T_fulltext (title) VALUES (' Shanghai World Expo prepares to enter substantive operation faces five challenges (17:03) ')
Insert into T_fulltext (title) VALUES (' Taiwan media said Chen Shui-bian's New Year's message will be the same old (15:30) ')
Insert into T_fulltext (title) VALUES (' Pearl river delta salty Tide impact area less than last year (15:12) ')
Insert into T_fulltext (title) VALUES (' Shanghai Residents Experience rail Transit line Line 4 train (Photos) (14:53) ')
Insert into T_fulltext (title) VALUES (' Guangzhou billion capital fund-raising scam principal Wang Zhixhen was sentenced to death sentence (figure) (14:44) ')
Insert into T_fulltext (title) VALUES (' Comments: abolition of agricultural tax let people share economic growth results (17:30) ')
Insert into T_fulltext (title) VALUES (' Hong Kong media talks about America's new strategy to deal with China: cooperation and Prevention (15:57) ')
Insert into T_fulltext (title) VALUES (' interview with director of China Seismological Bureau Chen Jianmin: Earthquakes can be predicted (15:56) ')
Insert into T_fulltext (title) VALUES (' Comments: Savage bus mo Cheng Ugly image spokesperson (13:27) ')
Insert into T_fulltext (title) VALUES (' Taiwan media says the authorities and private enterprises are opposed to the civilian blessing (12:50) ')

=================== Figure 2 ===================
3: The Welcome interface appears as shown in Figure 3

=================== Figure 3 ===================
4: Select a unique index for the Full-text index, as shown in Figure 4

=================== Figure 4 ===================
5: Select the full-text indexed columns, note that a table can only be built with one Full-text index. Built
Do not choose the language of the word breaker, this will actually use the default word-breaker language. As shown in Figure 5.
By running
sp_configure ' default Full-text language '
You can view the default hyphenation language, like running the previous statement on my machine, the config_value value is 2052
It represents simplified Chinese.
Other code represents a language that can refer to the "Default Full-text language" keyword in book online.
Because SQL Server is not very clever with Chinese hyphenation, you cannot rely on SQL Server to bring you a very intelligent search, which means that your full-text search results may have records that you do not want to appear, or records that you want to appear that do not appear.

=================== Figure 5 ===================
6: Assign the table to the Full-text catalog, as shown in Figure 6, or you can create a new directory from this interface.

=================== Figure 6 ===================
7: The table will be prompted to fill the schedule, in order to be able to accurately real-time to the base table of the Full-text indexed changes (add, delete, change) to apply to the Full-text index in real time, it is recommended to use the other methods described below for index maintenance. As shown in Figure 7, it is recommended that you do not add any schedules here and click "Next" directly.
If the data is not modified again, no additional type of table-level padding will be added after a full population.
If you implement this step and you no longer add any form of table scheduling, updates to the base table data will not be updated to the Full-text index.

=================== Figure 7 ===================
8: Click "Finish" as shown in Figure 8 to complete the interface prompts, please wait until the next screen appears.

=================== Figure 8 ===================
9: Click "OK" in the interface shown in Figure 9.
Because no schedules have been added to the table, the settings need to continue.

=================== Figure 9 ===================
10: Right-click the F_fulltext table in Enterprise Manager, as shown in Figure 10, and select change tracking.
After you select Change tracking, modifications to the base table data can be reflected in the Full-text index.
Special note: Once change tracking is selected, the table must be fully populated, based on experience.
If the Full-text indexing database is used online, selecting change tracking can result in a rapid performance degradation that results in Full-text indexing
cannot be used.

=================== Figure 10 ===================
11: Right-click the F_fulltext table in Enterprise Manager, as shown in Figure 11, and select Update Index in background.
By this step, the Full-text index is completely complete. Because we selected change tracking, the full population of the table is actually started.
As a rule of thumb, a table with a length of 3 million Full-text index characters 50 takes 8-10 hours to complete a full population.

=================== Figure 11 ===================
12: You can view the properties of the Full-text catalog by double-clicking the Full-text index in the Full-text catalog, as shown in Figure 12, the status is idle, and the project count is greater than 0, which means that the full population is complete.

=================== Figure 12 ===================
13: How to do full-text Indexing query (simple introduction)
Full-Text indexing queries recommend using the CONTAINS and FREETEXT predicates for queries.
If the keyword includes only noise words (for information about noise words, see the "noise word" keyword in book online)
SELECT * from T_fulltext where contains (title, ' 3 ')
The following error will be reported
Server: Message 7619, Level 16, State 1, line 1
The Full-text operation failed to run. The query clause contains only the words that are ignored.
Use the following syntax to avoid errors and to actually query the correct records.
SELECT * from T_fulltext where contains (title, ' *3* ')
13 media said Chen Shui-bian's New Year greeting will be the same (15:30) 2005-12-31 23:17:17.670
10 Beijing public Security system today all stop Hugh Check Five rings inside the Gun (17:30) 2005-12-31 23:17:17.653
17 Comments: Abolition of agricultural tax to share the fruits of economic growth (17:30) 2005-12-31 23:17:17.670
9 Beijing Large Capacity bus operation first day encounter passengers full (17:30) 2005-12-31 23:17:17.653
8 deaths of patients in Fujian, Guangxi, hospital responsible Persons handled (17:33) 2005-12-31 23:17:17.653
(The number of rows affected is 5 rows)
A brief introduction to Full-text indexing is here, hoping to play a role.