How to use SQL Server 2000 full-text search

I. How to enable full-text search in SQL
1. Verify that the full-text search service is installed
You can use the Stored Procedure fulltextserviceproperty (return information about full-text service level attributes) to verify whether the search service (full-text component) is installed. For example, select fulltextserviceproperty ('isfulltextinstalled'): 1 indicates that the full-text component is installed; 0 indicates that the full-text component is not installed; null indicates that the input is invalid or an error occurs.
2. Start the full-text search service (Mircosoft search)
3. Support for enabling full-text database indexing
Enable the database for full-text indexing:
Use northwind
Exec sp_fulltext_database 'enable'
Delete all directories from the database:
Use northwind
Exec sp_fulltext_database 'disable'
2. Create a full-text directory
1. Create a full-text directory
Use northwind
Exec sp_fulltext_catalog 'ft _ catalog ', 'create'
2. Recreate the full-text directory
Use northwind
Exec sp_fulltext_catalog 'ft _ catalog ', 'rebulid'
3. start full-text directory Filling
Use northwind
Exec sp_fulltext_catalog 'ft _ catalog ', 'start _ full'
4. Stop filling the full-text directory.
Use northwind
Exec sp_fulltext_catalog 'ft _ catalog ', 'stop'
5. Delete the full-text directory
Use northwind
Pk_products
Exec sp_fulltext_catalog 'ft _ catalog ', 'drop'
3. Create full-text index data elements for the table
1. Unique index created
Use northwind
Exec sp_fulltext_table 'products', 'create', 'ft _ catalog ', 'pk_products'
2. Set full-text index column names
Use northwind
Exec sp_fulltext_table 'products', 'create', 'ft _ catalog ', 'pk_products'
3. Set full-text index column names
Use northwind
Exec sp_fulltext_column 'products', productname ', 'add'
Go
Exec sp_fulltext_column 'products', quantityperunit ', 'add'
Go
4. Activate full-text indexing
Use northwind
Exec sp_fulltext_table 'products', 'activate
5. Fill in the full-text INDEX DIRECTORY
Use northwind
Exec sp_fulltext_catalog 'ft _ catalog ', 'start _ full'
4. Use Transact-SQL for full-text search. We use predicates such as contains and freetext.
1. Retrieve all columns in productname that contain B and C.
Use northwind
Select *
From Products
Where contains (productname, '"C *" and "B *"')
2. Retrieve all columns in productname that contain one of Chai Chang tofu.
Use northwind
Select *
From Products
Where freetext (productname, 'chai Chang tofu ')
5. Full-text query-related system stored procedures
(1) Start the full-text processing function of the database (sp_fulltext_datebase );
(2) create a full-text directory (sp_fulltext_catalog );
(3) register the table (sp_fulltext_table) that requires full-text indexing in the full-text directory );
(4) Name of the full-text search column in the table (sp_fulltext_column)
(5) create a full-text index for the table (sp_fulltext_table );
(6) Fill in the full-text index (sp_fulltext_catalog ).

Full-text query support
Microsoft SQL Server 2000 uses the full-text provider to retrieve the required information from the Microsoft Search Service when receiving a Transact-SQL statement with a full-text structure. The full-text construction is a ins or freetext predicate, or a containstable or freetexttable rowset function. If you do not know the columns that contain search criteria, You can reference multiple columns in the full-text index. Shows the process.

The process involves the following steps:
The application sends a full-text Transact-SQL statement to the SQL server instance.
The SQL Server relational engine queries a table to verify the full-text structure and determines whether the full-text index overwrites the column reference. Relational engine simplifies each SQL statement into a series of row set operations, and uses OLE DB to pass these operations to basic components, usually the storage engine. The relational engine converts any full-text constructor to a row set request through the full-text provider rather than the storage engine. The requested row set is a key set that meets the search criteria and levels. This level indicates the degree to which data of each key meets the search criteria. The line Set Request command sent to the full-text Provider includes the full-text retrieval conditions.
The full-text provider verifies the request and changes the search condition to the form used by the query support component of the Microsoft search service. Send the request to the search service.
The query component can use the search engine component to retrieve the requested data from the full-text index. The data is then transmitted back to the full-text provider in the form of a row set.
The full-text provider returns this row set to the relational engine.
The relational engine combines all row sets received by the storage engine and the full-text provider to generate the final result set sent back to the client.
 
Full-text directory and Index
Microsoft SQL Server 2000 full-text indexing provides effective support for complex word search in string data. Full-text indexes store information about important words and their locations in specific columns. Full-text query uses this information to quickly search for rows containing a specific word or a group of words.
Full-text indexes are included in the full-text directory. Each database can contain one or more full-text directories. A directory cannot belong to multiple databases, and each directory can contain full-text indexes of one or more tables. A table can have only one full-text index. Therefore, each table with a full-text index belongs to only one full-text directory.
Full-text directories and indexes are not stored in the databases to which they belong. Directories and indexes are managed separately by the Microsoft search service.
Full-text indexes must be defined on the base table, but not on the view, system table, or temporary table. Full-text indexes are defined as follows:
Columns (primary keys or candidate keys) that uniquely identify each row in the table, and null values are not allowed.
One or more string columns covered by the index.
Full-text indexes are filled with key values. Each key item provides information about the important words associated with the key (except the interfering words or Terminator words), their columns, and their position in the column.
Formatting text strings (such as Microsoft Word document files or HTML files) cannot be stored in strings or Unicode columns because many of these files contain data structures that do not constitute valid characters. Database applications may still need to access the data and retrieve the full text of their applications. Because the image column does not require each byte to form a valid character, many sites store this type of data in the image column. SQL Server 2000 introduces the ability to perform full-text searches for these types of data stored in the image column. SQL Server 2000 provides filtering and can extract written data from Microsoft Office (.doc、.xls and. PPT file (.txt) and HTML file (.htm. When designing a table, in addition to the image column for storing data, you must also include the file extension of the format of the data stored in the image column to bind the column. You can create full-text indexes that reference the image column and bind the column to enable full-text search on the localized information stored in the image column. In SQL Server 2000, the full-text search engine uses the file extension information in the binding column to select an appropriate filtering method for retrieving regionalized data from the column.
Full-text indexing is a component used to execute two Transact-SQL predicates to test rows based on full-text search conditions:
Contains
Freetext
Transact-SQL also contains two functions that return the row set that meets the full-text search criteria:
Containstable
Freetexttable
SQL server sends search criteria to Microsoft search. The Microsoft Search Service searches for all keys that match the full-text search criteria and returns them to SQL Server. SQL Server then uses the key list to determine the rows to be processed in the table.
Full-text index
Full-text support for Microsoft SQL Server 2000 data involves two features: the ability to query character data and the ability to create and maintain basic indexes to simplify these queries.
Full-text indexes are different from normal SQL indexes in many places.

Full-text index storage of common SQL indexes is controlled by the database where they are defined. Stored in the file system, but managed through the database. Each table can have several common indexes. Each table can have only one full-text index. When data is inserted, updated, or deleted as its basis, it is automatically updated. Adding data to a full-text index is called filling. A full-text index can be requested by scheduling or specific requests, or automatically generated when new data is added. Do not group. Groups one or more full-text directories in the same database. Use the SQL Server Enterprise Manager, wizard, or transact-SQL statement to create and remove SQL statements. Create, manage, and remove SQL Server Enterprise Manager, wizard, or stored procedure.

These differences make a large number of management tasks indispensable. Full-text management is implemented at several levels:
Server
You can set certain attributes of the server range (such as resource_usage) to increase or decrease the number of system resources used by full-text services.
 
The full-text engine runs on Microsoft Windows NT Server and Microsoft Windows 2000 Server as a service named Microsoft search. Microsoft Search Service is unavailable for Microsoft SQL Server Personal Edition. Although this means that the Microsoft Search Service is neither installed on Microsoft Windows 95/98 nor on a Windows NT Workstation or Windows 2000 Professional client, however, these clients can use this service when connecting to the SQL Server Standard Edition installation or Enterprise Edition instance.
Database
You must enable the database to use the full-text service. Metadata of one or more full-text directories can be created and removed from an enabled database.
Full-text directory
The full-text directory contains the full-text index in the database. Each directory can be used to index one or more tables in the database. The indexes in this directory are filled with the management functions described here. (The full-text directory must reside on the local hard drive associated with the SQL server instance. Removable drives, floppy disks, and network drives are not supported ). Up to 256 full-text directories can be created on each server.
 
This indicates that full-text indexing is fully supported in the Windows NT failover cluster environment. For more information, see run full-text query in the Failover group.
Table
First, you must enable table for full-text support. Then, create metadata (such as the table name and its full-text directory) for the full-text index associated with the table ). After the table is enabled, you can fill it with data in columns enabled for full-text support. If the full-text definition of a table is changed (for example, adding a new column that will also be indexed for full-text search ), you must refill the full-text directory to synchronize the full-text index with the new full-text definition.
Column
You can add or remove columns that support full-text queries from the inactive registry.
At all these levels, you can use tools to retrieve metadata and status information.
Like conventional SQL indexes, full-text indexes can be automatically updated when data is modified in related tables. Alternatively, you can manually refill the full-text index at appropriate intervals. This type of rewriting may be time-consuming and resource-consuming. Therefore, when there are few database activities, it is usually an asynchronous process running in the background.
Tables with the same update characteristics (such as tables with fewer changes and with more changes or frequently changed during a specific period of the day) should be combined, and allocate it to the same full-text directory. By configuring full-text directory filling Scheduling in this way, full-text indexes and tables are synchronized, and there is no negative impact on the resource usage of the database server when there are many database activities.
It is very important to arrange the location of the full-text index for the tables in the full-text directory. When specifying a table for the full-text directory, pay attention to the following basic principles:
Always select the minimum unique index that can be used for the full text unique key. (The four-byte and integer-based index is the best .) This will significantly reduce the resources required by the Microsoft Search Service in the file system. If the primary key is large (more than 100 bytes), you can select another unique index (or create another unique index) in the table as the full-text unique key. Otherwise, if the size of the full-text unique key reaches the allowed upper limit (450 bytes), full-text filling will not continue.
If the table to be indexed has thousands of rows, specify the table to its own full-text directory.
The number of changes and the number of rows in the table for full-text indexing should be considered. If you want to change the total number of rows and the number of rows in the table exceeds thousands during the last full-text filling period, specify the table to its own full-text directory.