Full-text search function using SQL Server 2000
step:
1. Open the Northwind database, right-click Products table, select "Full-text Search", "Define Full Text Index on Table" to create a full-text directory. In the Full-Text Index Wizard, select the column you need full-text query criteria (you can be a text or image), we choose ProductName and QuantityPerUnit columns. Then enter the next step, enter a new directory Procatalog, click Next. In the fill schedule, we do not establish a fill schedule. Then, "Next", full into full-text index pole. After completing, we can see that in the "full-text catalog", there is more full-text directory Procatalog. The right key Procatalog property can see its status, table, and scheduling.
2. Right-click the full-text directory Procatalog, "Start full fill", and the system will appear "full-text directory fill success".
3. At this time, we can use Transact-SQL to retrieve the full text, we use Contains, Freetext and other predicates. Such as:
Retrieve in ProductName, that is, all columns containing B but also
SELECT *
From product
WHERE Contains (ProductName, '"C *" and "b *")
Retrieve ProductName, which contains all columns in one of Chai Chang Tofu
SELECT *
From product
WHERE FREETEXT (ProductName, 'Chai Chang Tofu ")
We can also use ContainStable, FreetextTable, etc.
4, we can create a web or form client with .NET, we provide a good full-text search interface to expand your application.
reference:
Full text inquiry related system stored procedures
(1) Start the full-text processing function of the database (SP_FULLTEXT_DATEBASE); (2) Establish a full-text directory (sp_fulltext_catalog); (3) Register in the full-text directory requires a full-text index (sp_fulltext_table); (4) indicates that the full-text retrieval is required. Column name (sp_fultext_column) (5) creates a full-text index (sp_fulltext_table) for the table; (6) Full-text index (sp_fulltext_catalog).
Full text inquiry support
Microsoft® SQL ServerTM 2000 uses a full-text-SQL statement that uses a full-text provider to retrieve the required information from the Microsoft Search Service. The full text constructor is a Contains or Fretext predicate, or a ContainStable or FreEtextTable rowset function. If you don't know the columns containing the search criteria, the full-text construct can reference multiple columns in full-text indexes. The process of this process is shown below.
The steps involved in this process include:
The application sends a TRANSACT-SQL statement with full text to the SQL Server instance.
The SQL Server relationship engine determines whether the full-text index overwrites column references by querying the system table verification. The relational engine simplifies each SQL statement into a series of row-of-line operations, and uses OLE DB to pass these operations to the basic components, usually the storage engine. The relational engine converts any full-text construct into a request for rowset through a full-text provider instead of a storage engine. The requested rows is a key set that meets the search criteria, and this level represents the degree of each key to meet the degree of search criteria. The rowset request command sent to the full-text provider includes full-text search criteria. Full-text provider verification requests and changes the search criteria to the form used by the Query Support Components of the Microsoft Search Services. Send the request to the search service.
The query support component uses the search engine components from full-text index to extract the requested data. These data are then passed back to the full text provider in the form of rows.
The full-text provider returns this rowset to the relational engine.
The relational engine combines it from all rows sets received from the storage engine and full-text provider to generate the final result set of sending back to the client.
Full-text catalog and index
Microsoft® SQL ServerTM 2000 full-text index provides effective support for complicated word search in string data. Full text index store information about the position of important words and these words in a particular column. The full text query uses this, quickly search for a line containing a specific word or a word.
The full-text index is included in the full-text catalog. Each database can contain one or more full-text directories. A directory cannot belong to multiple databases, and each directory can contain a full-text index of one or more tables. A table can only have a full-text index, so each table with a full-text index belongs to a full-text directory.
The full-text directory and index are not stored in the database they belong. Directory and index are managed by Microsoft Search Services.
Full text index must be defined on the base table without defining on the view, system table, or temporary table. The definition of full-text index includes:
It is the only identifier (primary key or candidate key) of each row in the table, and the NULL value is not allowed.
One or more characters covered by the index.
Full text index fills the key value. The items of each key provide important words (except interfering words or terminates) associated with the key, and their columns and their positions in the columns.
Formatting text strings (such as Microsoft® WordTM document files or HTML files) cannot be stored in a string or Unicode column, because many bytes in these files contain data structures that do not constitute a valid character. Database applications may still need access to these data and apply a full-text search. Because the image column does not require each byte to form a valid character, many sites are stored in the Image column. SQL Server 2000 introduces the ability to perform full-text retrieval of these types of data stored in the Image column. SQL Server 2000 provides filtering, detail textual data from Microsoft OfficeTM files (.doc, .xls, and .ppt files), text files (.txt files), and HTML files (.htm files). In addition to the Image list including the saved data, the design table is required to include a file extension stored in the data format stored in the Image column. You can create a full-text index of reference image columns and binding columns to enable full-text retrieval on textual information stored in the Image column. The SQL Server 2000 full-text search engine uses the file extension information in the binding column, selects the appropriate screening of the quaternary data from the column.
Full-text index is a component used to perform two Transact-SQL predicates to test lines according to the full-text search criteria:
Contains
Fretext
Transact-SQL also contains two functions that returns a rowset that meets the full-text retrieval conditions:
ContainStable
FreetextTable
SQL Server sends the search criteria to the Microsoft search service internally. Microsoft Search Services look for all keys that meet the full-text search criteria and return them to SQL Server. SQL Server then uses a list of keys to determine the row to process in the table. Full-text index
Full-text support for Microsoft® SQL ServerTM 2000 data involves two functions: the capabilities of the character data are queried and created and maintained the base index to simplify these queries.
Full-text indexes differ in many places to ordinary SQL indexes.
Ordinary SQL index
Full-text index
Defined when storing the control of their database.
Stored in the file system but managed by database.
Each table allows several ordinary indexes.
Each table only allows a full-text index.
They are automatically updated when inserting, updating, or deleting data as their basis.
Add data to full-text index name fill, full-text index can request scheduling or specific requests, or automatically occur when adding new data.
Not group.
Packet in the same database is one or more full-text directories.
Create and remove using the SQL Server Enterprise Manager, Wizard, or Transact-SQL statement.
Use SQL Server Enterprise Manager, Wizard, or stored procedures to create, manage, and remove.
These differences make a large number of management tasks indispensable. Full-text management is implemented at several levels:
server
You can set some attributes of the server range (such as resource_usage) to increase or reduce the number of system resources used by the full-text service.
Note The full-text engine runs on Microsoft Windows NT® Server and Microsoft Windows® 2000 Server as a service called Microsoft Search as a Microsoft Windows NT® Server and Microsoft WINDOWS® 2000. For Microsoft SQL Server Persons, Microsoft Search Services is not available. Although this means that the Microsoft Search Service is not installed on Microsoft Windows 95/98, it is not installed on a Windows NT workstation or Windows 2000 Professional client, but these clients are connected to the SQL Server standard installation or an enterprise instance. You can use this service.
database
A database must be enabled to use full-text services. Metadata of one or more full-text directories can be created and removed in the enabled database.
Full-text catalog
The full-text directory contains the full text index in the database. Each directory can be used for indexing requirements of one or more tables in the database. The index in this directory is filled with the management functions described here. (The full-text directory must reside on a local hard drive associated with the SQL Server instance. No moving drive, floppy disk, and network drives are not supported. You can create up to 256 full-text directories on each server.
Note The Windows NT failover cluster environment fully supports full-text index. For more information, see Run the full text inquiry in the failover cluster.
table
First, you must have a full-text support. Then, the metadata (such as the table name and its full-text directory) is created for the full-text index associated with the table. After the table is enabled, it can be filled with the data in the columns enabled for full-text support. If the full text definition of the table is changed (for example, the new column that will be indexed for full-text retrieval), you must re-fill the relevant full-text directory to define synchronization with new full-text.
Column
Columns that support the full text query can be added or removed from the non-active registry.
On all these levels, tool retrieval metadata and status information can be used.
Like a conventional SQL index, the full-text index can be automatically updated when modifying the data in the related table. Alternatively, the full-text index can also be re-filled by appropriate intervals. This rewrite may consume a large amount of resources, so this is usually running in the background when database activity is less.
A table with the same update features (such as changes with changes, more than changing, or frequently changed frequently in a certain period of time), and assigned to the same full-text directory. By setting a full-text catalog fill schedule, the full-text index and the table remain synchronized, and the database activity does not have a negative impact on the use of the resources of the database server. It is very important to arrange the full text index of tables in the full-text catalog. When specifying a table for a full-text catalog, you should pay attention to the following basic principles:
Always choose a minimum unique index that can be used for full-text unique keys. (4 bytes and integer-based indexes are optimal.) This will significantly reduce the resources required by Microsoft Search Services in the file system. If the primary key is large (more than 100 bytes), you can consider the other unique index (or create another unique index) as a full-text unique key. Otherwise, if the full-text unique key reaches the permissible upper limit (450 bytes), full-text fill will not continue.
If the table is indexed, there is thousands of lines, specify this table to its own full-text directory.
The number of changes that occur in the table that can be used for full-text index should be considered. If you want to change the total number of lines, plus the number of rows that appear in the previous full-text fill period reach thousands of lines, specify this table to its own full-text directory.
See
sp_fulltext_table
Contains
A predicate, which is used to search for columns containing characters based on characters, the column is accurate or blurred (not too accurate) matching with individual words and phrases, and approximate words within a certain range of other words. Contains can be searched:
Words or phrases.
Word or phrase prefix.
The word near the other word.
The words generated by the word change of the other word (for example, the word Drive are Drives, Drive, Driving, and Driven Words).
Have higher weight than the other word.
grammar
Contains ({Column | *}, '
Fretext
It is a predicate that searches for columns containing characters based on characters, where the value is in line with the meaning of the text specified in the search criteria, but does not comply with the expression. When using FreeText, the full-text query engine splits freetext_string into several search terms and gives each word with different weighting and then looks up.
grammar
Freetext ({Column | *}, 'Freetext_String')
ContainStable and FreetextTable functions are used to specify a full-text query that returns relative rankings for each row. These two functions are very similar to the full-text predicate Contains and Fretext, but the usage is different.
(Some of this article from MSDN)
Author: Li Honggen
E-mail: lihonggen0@163.com
