Full-text index is a better solution to solving massive data fuzzy query. Full text retrieval can retrieve varchar, text, image field, but a table can only build a full-text index SQL Server 2000 introduced the ability to perform full-text retrieval of these types of data stored in the Image column.
If there is no full-text index, the fuzzy query for characters can only be scanned (or index scanning) for the base table, and the blur query needs to be scanned to the full-table scan or indexing means consumes a lot of IO. If the fuzzy query often occurs, it will cause the database performance to deteriorate.
This article is simple, only in the VARCHAR type field, the graphic demonstration is very complete and the efficient and feasible full-text index creation and maintenance process. 1: Expand the database to establish a full-text index in Enterprise Manager, right-click full-text directory ", Select" New Full Directory "as shown in Figure 1. You can modify the path to the full-text directory, enhance efficiency by placing a full-text directory on a separate disk or disk array. It is recommended not to make any form of fill schedule for the entire full-text directory.
=================== Figure 1 ============================= 2: Next to add a full text to the specific table index. Full-text index cannot be built on the view, only on the basis table with a unique index (primary key is also a unique index). The following 2 demonstrates how to establish a full-text index on the table.
The script of the T_FullText table in the demo and the script of the initialization data are as follows:
create table t_fulltext (rid int identity (1,1), title varchar (256) not null, writetime datetime default (getdate ())) goalter table t_fulltext add constraint pk_fulltext primary key (rid) go-- initialization data insert into t_fulltext ( Title) VALUES ('Shandong Penglai River Western Gold Mine Transmission Accident 2 people dead 3 people missing (21:29)') Insert Into T_FullText (Title) VALUES ('Thailand will set up Consulate General (21:10)') Insert in Nanning, Guangxi INTO T_FULLTEXT (TITLE) VALUES ('Hebei Anhui and other 12 provinces to reduce agricultural tax, etc. (20:43)') Insert Into T_FullText (Title) VALUES ('Taiwan scholar Dream predicts Chen Shui Bage New Year's Day Speech (20:04)') Insert INTO T_FULLTEXT (TITLE) VALUES ('Macau Bo Houqi issued New Year's Eve to greet the New Year (20:02)') Insert Into T_FullText (Title) Values ('southern water, the first completed project Shandong Duan Jiping Dry Dry Dry Dry Success (19:48) ') INSERT INTO T_FULLTEXT (TITLE) VALUES (' Photos: Beijing 110 motorcycle patrol debut (18:07) ') INSERT INTO T_FULLTEXT (Title) VALUES (' Fujian Guangxi Patient Death Hospital Death Hospital Responsible (17: 33) ') INSERT INTO T_FULLTEXT (TITLE) VALUES (' Beijing Large Capacity Bus Operations Insert (17:30) ') Insert Into T_FullText (Title) VALUES (' Beijing Public Security System Today, Today Insert INTO T_FULLTEXT (Title) Values (') found that Hailong genome enzymes have killing effects (17:27)') Insert Into T_FullText (Title) VALUES ('Shanghai World Expo Enter substantive operations face five challenges (17:03) ') Insert Into T_FullTex T (Title) VALUES ('Taiwan Media said Chen Shui-bun New Year's greetings must return to heavy (15:30)') INSERT INTO T_FULLTEXT (TITLE) VALUES ('Pearl River Delta salty impact influence is less than last year (15:12)') Insert INTO T_FULLTEXT (Title) VALUES ('Shanghai Citizen Experience Rail Transit Train (Photos) (14:53)') Insert Into T_FullText (Title) Values ('Guangzhou billion yuan fund-raising fraud case main committee Wang Riheng was sentenced) (14:44) ') Insert Into T_FullText (Title) VALUES (' Comments: Cancellation of agricultural taxes to share economic growth results (17:30) ') INSERT INTO T_FULLTEXT (Title) Values (' Hong Kong Media talks about China to respond to China Strategy: Cooperation and Prevention (15:57) ') Insert Into T_FullText (Title) VALUES (' Interview with Director Chen Jianmin "Earthquake is predicted (15:56) ') Insert Into T_FullText (Title) Values (' Comments : Barbarous Mo Cheng City Ugly Image Spokesperson (13:27) ') Insert Into T_FullText (Title) VALUES ('
Taiwanese media said that the authorities and private companies opposite the blessings of the people (12:00) ') ======================================== =========
3: The welcome interface of the appearance is shown in Figure 3 ==================================================
4: Select a unique index for full-text index, as shown in Figure 4 ================================================ ==
5: Select the column of the full-text index, pay attention to a table can only build a full-text index. It is recommended not to select the language of the word, which actually uses the default word language. As shown in Figure 5. By running sp_configure 'default full-text loguage'
You can view the default word language, such as running the upper statement on my machine, the config_value value is 2052 which represents Simplified Chinese. The language represented by other codes can check the "Default Full-Text Language" keywords for Book Online.
Since SQL Server is not very high, it doesn't rely on SQL Server to bring you a very smart search, which means your full-text search results, there may be no records, or you want to have records appear. =================== Figure 5 ============================ 6: assign the table to the full-text directory, such as Figure 6 shows the new directory can also be created through this interface. =================== Figure 6 =======================
7: Next prompts the table to fill the table, to apply the modification (increasing, deletion, removal) of the full-text search of the base table to the full-text index, which is recommended to use the other methods described below to index maintain. As shown in Figure 7, it is recommended not to add any scheduling here, click "Next".
If the data will not be modified, then after full fill, there is no need to add other types of table-level padding. If implemented to this step, no additional form of table scheduling is added, then the base table data will not be updated to full-text index. =================== Fig. 7 ===================================1 In the interface prompt, click "Finish", wait a little until the next interface appears. =================== Figure 8 =============================================================================================================================================================================
9: Click "OK" in the interface shown in Figure 9. Since it has not been added to add any dispatch, continue settings. =================== Figure 9 ======================
10: Right-click on the F_FullText table in the Enterprise Manager, as shown in Figure 10, select "Change Track". After selecting the change track, the modification of the base table data will be reflected in the full-text index. Special Note: Once "Change Tracking" is selected, depending on the experience, it is actually fully filled. If it is a full-text index database used online, select "Change Tracking" will result in rapid performance, resulting in full-text indexing.
=================== FIG. 10 ==========================================================================================================================================================================
11: Right-click the F_FullText table in the Enterprise Manager, as shown in Figure 11, select "Update index in the background". To this step, the full-text index is completely completed. Since we selected "Change Tracking", the full fill of the table is actually started. According to experience, 3 million full-text professionals have a table for 50 tables, and it takes 8 to 10 hours to complete completely filled.
=================== FIG. 11 ======================================================================================================================================================
12: You can view the properties of the full-text directory by double-click the full-text directory, as shown in Figure 12, the status is "free", and the project count is greater than 0, the full fill has been completed.
=================== Fig. 12 ============================= 13: How to make a full-text index query (simple only Introduction) The full-text index query is recommended to use the Contains and FreEtext predicates to query. If the keyword includes only interfering words (for information on interfering words, see the "Interfering Word" Keyword "Select * from T_FullText Where Contains (Title, '3') will report the following error
Server: Message 7619, Level 16, State 1, Row 1 full-text operation failed. The query subsee is only included with the ignored words.
Use the following grammar to avoid errors, and can indeed query the correct record.
Select * from t_fulltext where contacts (title, '"* 3 *") 13 media said Chen Shui-bun New Year's wish word will return to heavy (15:30) 2005-12-31 23: 17: 17.67010 Beijing public security system all stop today严 查 五 内 (17:30) 2005-12-31 23: 17: 17.65317 Comments: Cancellation of agricultural taxes to share economic growth results (17:30) 2005-12-31 23: 17: 17.6709 Beijing large capacity The first day of the bus is full of passengers (17:30) 2005-12-31 23: 17: 17.6538 Patients with Patients in Guangxi, Fujian Province, were processed (17:33) 2005-12-31 23: 17: 17.653 The number of rows affected is 5 lines)
The simple introduction of the full-text index is here, I hope to get a role of a throwing brick, you can visit the author's contact information by visiting http://chinadba.cn, welcome to discuss. This article is original, please call, please keep this article. Write: 2006-1-1
TRACKBACK: http://tb.blog.9cbs.net/trackback.aspx?postid=618511
