The standard Edition and Enterprise Edition versions of SQL Server 2000 provide the complex retrieval of strings in table data---full-text search (full-text searches). This feature needs to be added at installation time through custom installation options.
10.7.1 full-text indexing and directory
Full-text Search is accomplished by using Full-text indexing (Full-text index). A Full-text index stores information such as the exact meaning of a string in the table's data and its position in the table's columns. Full-Text Search is the use of the information in the Full-text index to find rows containing a particular string of data.
The
Full-text index is contained in a full-text indexed directory (Full-text catalogs), typically a full-text indexed catalog of one or more tables in the same database. A table can have only one Full-text index, so each table that has a Full-text index is subordinate to only one Full-text index catalog. A database can contain one or more full-text indexed catalogs, but a full-text indexed directory can only be subordinate to one database. Full-text indexed directories and Full-text indexes, although managed by SQL Server, are not stored in the database file, but are operating system files associated with the Microsoft Search Service.
When using full-text indexing queries, SQL Server sends the retrieval criteria to the Microsoft Search service. The Microsoftsearch service will find all the values that match the Full-text search criteria and return them to SQL Server SQL Server to determine which rows of data will be processed by the table. Therefore, you must start the Microsoft Search service to
enables SQL Server to have full-text search capabilities.
There are two ways to start the Microsoft Search Service:
(1) starts with Enterprise Manager
in Enterprise Manager's support Services directory tree folder, right-click "Full-textsearch" and select "Start" from the shortcut menu to start. Start as shown in Figure 10-1.
(2) starts with the service management program in the operating system control Panel
in Win2000 Server and Windows NT Confirmation, you can right-click the service you want to start Microsoft Search from the Services interface under Administrative Tools administrative tool in Control Panel, Selecting the Start option from the shortcut menu launches the Microsoft Search Service, which is shown in Figure 10-2 after the start of the screen.
10.7.2 Create a full-text indexing catalog
The following steps to create a full-text indexed catalog in Enterprise Manager:
(1) In Enterprise Manager, select the database in the directory tree where you want to create the Full-text indexed directory, expand it, and right-click the Full-text catalogs object to choose New Full-text Catalogs from the shortcut menu, which appears as shown in Figure 10-3 The new Full-text Indexing Directory dialog box shown in.
(2) In the dialog box shown in Figure 10-3, enter the name of the Full-text index directory to create and the location where the files are stored the default directory for SQL Server is "install directory \mssql\ftdata", where you will create a separate directory for each full-text indexed directory. The directory name differs from the name specified in Figure 10-3 and is generated by the system. Click the Schedules page box, and the Full-text Indexing Directory Plan dialog box appears as shown in Figure 10-4.
(3) in Figure 10-4, you can create and edit the full-text indexing Catalog execution plan. Click the OK button to create the Full-text indexing catalog.
Figure 10-4 Create a full-text Indexing Catalog Schedule dialog box
10.7.3 Create a full-text index
The following steps to create a Full-text index:
(1) In Enterprise Manager, select the database for which you want to create a Full-text index, or if you right-click and choose New Full-text Indexes from the shortcut menu, the Full-text Indexing Creation wizard interface appears as shown in Figure 10-5.
(2) Click the Next button to appear as shown in Figure 10-6 to select the Table dialog box to create a Full-text index.
(3) Click the Next button to appear the Unique Index dialog box in the Select table as shown in Figure 10-7.
(4) Click the Next button to appear in the dialog box for the selected column that makes up the index, as shown in Figure 10-8.
(5) Click the Next button to see the selection or New Full-text Indexing Directory dialog box shown in Figure 10-9.
(6) Click the Next button to see the selection as shown in Figure 10-10 or to create a new editing Full-text index catalog. Execution Plan or Table Plan dialog box. Click the new Catalog Schedule button, and the new Full-text Indexing Directory Execution Plan dialog box appears as shown in Figure 10-11. The other edit, New Execution Plan dialog box is similar to this.
Figure 10-8 dialog box to select a column to build a full-text index
Figure 10-11 New full-text Indexing Catalog Execution Plan dialog box
(7) Click the Next button and the end full-Text Indexing Creation wizard dialog box appears as shown in Figure 10-12. When you click the Finish button, SQL Server starts running the program in the background, defining the Full-text index, and displays the Success dialog box for the definition of Full-text indexing as shown in Figure 10-13 after the program completes. This dialog box tells the user that the Full-text index is already defined, but has not yet implanted a key value.
(8) Click the "OK" button. In Enterprise Manager, select the Full-text index directory where you created the Full-text index, right-click, and choose Start Full Population from the shortcut menu to complete the creation of the Full-text index.
Note: full-text indexes, unlike normal indexes, are not updated with data, so you must update it frequently or with an execution plan, which can consume many system resources and time.
Figure 10-13 defines a Full-text Indexing Success dialog Box
10.7.4 full-text Search Query
after you have created a Full-text index in a table, you can retrieve the data in a table for full-text retrieval. Full-text search needs to be
to use two Transact-SQL predicates in the WHERE clause of the SELECT command: CONTAINS and FR
ETEXT.
(1) CONTAINS
The syntax of the CONTAINS 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 implication is that the retrieval column contains information that conforms to the < contains_search_condition > retrieval criteria. which
"COLUMN_NAME" must be a column contained in a Full-text index, and "*" means that the search condition applies to all columns contained in the Full-text index. The meaning of the CONTAINS predicate is similar to the LIKE predicate, but the CONTAINS predicate is more powerful, supports more queries, and is insensitive to capitalization.
Summary of this chapter
Most of this chapter covers the application of the SELECT statement. Proficiency in the SELECT statement is equivalent to conquering half of the SQL language. If you can use the statements in the next chapter, "Database Update", you will have a basic command of the SQL language.
