Standard Edition and Enterprise Edition of SQL Server 2000 provide complex search functions for strings in table data-full-text search (full-text SEAR)

The Standard Edition and Enterprise Edition editions of SQL Server 2000 provide the complex retrieval feature of strings in table data-full-text searches ). This feature needs to be added through custom installation options during installation.

10.7.1 full-text index and Directory

Full-text search is implemented by using full-text index. A full-text index stores strings with exact meanings in the table data and their positions in the columns of the table. Full-text search uses the information in the full-text index to find data rows containing specific strings.

Full-text indexes are contained in the full-text catalogs directory. Generally, full-text indexes of one or more tables in the same database constitute a full-text index directory. A table can have only one full-text index. Therefore, each table with a full-text index belongs to only one full-text index directory. A database can contain one or more full-text index directories, but one full-text index directory can only belong to one database. Although the full-text index directory and full-text index are managed by SQL Server, they are not stored in database files, but operating system files related to the Microsoft search service.

When full-text index queries are used, SQL server sends the search criteria to Microsoft search. The microsoftsearch service will find all values that meet the full-text search criteria and send them back to SQL Server. Based on these values, it will decide which data rows will be processed for the table. Therefore, you must start the Microsoft Search Service

Enables SQL Server to have full-text retrieval.

There are two ways to start the Microsoft Search Service:

(1) start with Enterprise Manager

In the support services directory tree folder of Enterprise Manager, right-click "full-textsearch" and select "start" from the shortcut menu to start the service. 10-1 after startup.

(2) start with the Service Management Program in the control panel of the Operating System

In Win2000 Server and Windows NT confirmation, you can right-click the service to be started on the "service" page under "Administrative Tools" "Administrative Tools" on the control panel, "Microsoft Search ", select "start" from the shortcut menu to start the Microsoft Search Service, as shown in Figure 10-2.

Create a full-text index directory in 10.7.2

To create a full-text index directory in Enterprise Manager, follow these steps:

(1) In the Enterprise Manager directory tree, select the database for which you want to create the full-text index directory and expand it, right-click the "full-text catalogs" object and select "new full-text catalogs" from the shortcut menu. The create full-text index directory dialog box is displayed, as shown in Figure 10-3.

(2) In the dialog box shown in Figure 10-3, enter the name of the full-text index directory to be created and the file storage location. The default storage directory of sqlserver is "installation directory mssqlftdata ", A separate directory is created for each full-text index directory. The directory name is generated by the system, which is different from the name specified in Figure 10-3. Click the "schedules" page. The full-text index directory plan dialog box from 10 to 4 appears.

(3) In Figure 10-4, you can create and edit the full-text index directory execution plan. Click OK to create the full-text index directory.

Figure 10-4 create a full-text index directory plan dialog box

10.7.3 create a full-text index

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

(1) In Enterprise Manager, select the database for which you want to create a full-text index, or right-click the table and choose new full-text indexes from the shortcut menu ", the full-text index creation wizard is displayed, as shown in Figure 10-5.

(2) Click "Next". The "select table to create full-text index" dialog box is displayed from 10 to 6.

(3) Click "Next". The "select a table" dialog box is displayed from 10 to 7.

(4) Click "Next" to display the dialog box for selecting the columns that make up the index from 10 to 8.

(5) Click "Next". The "select" or "create full-text index directory" dialog box from 10 to 9 appears.

(6) Click "Next" and select or create a new full-text index directory from 10 to 10. Execution Plan or table plan dialog box. Click the new catalog schedule button. The create full-text index directory execution plan dialog box from 10 to 11 appears. The other edit and create execution plan dialog boxes are similar.

Figure 10-8 dialog box for selecting columns for full-text indexing

Figure 10-11 create a full-text index directory execution plan dialog box

(7) Click "Next", and the complete full-text index creation wizard dialog box from 10 to 12 appears. Click "finish" to start running SQL server in the background.ProgramTo define the full-text index. After the program is completed, the success dialog box for defining the full-text index shown in 10-13 is displayed. This dialog box tells the user that the full-text index has been defined, but the key value has not been implanted.

(8) Click "OK. In Enterprise Manager, select the full-text index directory where the created full-text index is located, right-click, and select "start full population" from the shortcut menu to complete the full-text index creation process.

Note:The full-text index is different from the general index. It will not be updated with data updates. Therefore, it must be updated frequently or using an execution plan, and its updates will occupy a lot of system resources and time.

Figure 10-13 success dialog box for full-text index Definition

10.7.4 full-text search and query

After a full-text index is created in a table, you can search the full-text data in the table. Full-text search needs

The where clause of the SELECT command must contain two Transact-SQL predicates: contains and Fr.

Etext.

(1) contains

The syntax of the ins predicate is as follows:

Contains ({column_name | *}, '<contains_search_condition> ')

<Contains_search_condition >::=

{<Simple_term>

| <Prefix_term>

| <Generation_term>

| <Proximity_term>

| <Weighted_term>

}

| {(<Contains_search_condition>)

{And | and not | or} <contains_search_condition> [... n]

}

The meaning is that the search column contains information that meets the <contains_search_condition> Search Condition. Where

"Column_name" must be a column included in the full-text index. "*" indicates that the search condition is applied to all columns included in the full-text index. The meaning of the ins predicate is similar to that of the like predicate, but the contains predicate is more powerful and supports more query parties. The formula is case insensitive.

Summary of this Chapter

Most of the content in this chapter is about the application of select statements. Proficient in select statements is equivalent to conquering half of the SQL language. If you can use the statements in the Next Chapter "database update" skillfully, you can basically master the SQL language.