Use full-text search in SQL Server

Full-text indexes can be used in the SQL Server database. Unlike the like predicate that only applies to character patterns, full-text queries perform operations on words and phrases based on the rules of specific languages, thus, the language search is performed for this data.

When you query a large amount of unstructured text data, the performance advantages of full-text search are fully demonstrated. The like query for millions of lines of text data may take several minutes to return results. However, for the same data, full-text query takes only a few seconds or less, depending on the number of rows returned.

You can create full-text indexes for columns that contain char, varchar, and nvarchar data. You can also create full-text indexes for columns that contain formatted binary data, such as Microsoft Word documents stored in the varbinary (max) or image column. You cannot use the like predicate to query formatted binary data. To create a full-text index for a table, the table must have a unique and non-empty column.

The procedure for creating a full-text search is as follows:

1. Create a full-text directory. One full-text directory can contain multiple full-text indexes, but one full-text index can only be used to form one full-text directory. Each database can contain no full-text directories or multiple full-text directories. The as default parameter indicates that the full-text directory is the default full-text directory.

Create Fulltext catalog documentcatalog as default
2. Create a unique non-clustered index.

Create unique index t_documentscontent_verid on t_documentscontent (verid)
Generally, the primary key of the table you want to index in full text is used as the unique non-clustered index. This index will force the data in the inserted column to be unique. Generally, the primary key meets this requirement.

3. The last step is to create a full-text index.

Create Fulltext index on t_documentscontent (docucontent type column extendedname)
Key Index t_documentscontent_verid on documentcatalog
With change_tracking auto
T_documentscontent: Used to indicate full-text indexes
Docucontent: fields used for full-text search
If the field used for full-text search is a binary stream file, you must specify the column that provides the data by specifying the extension of the binary stream file. The type column extendedname in the preceding example indicates that the docucontent type is provided by the extendedname column.
Key index is used to specify the name of the unique key index in the full-text index table.
On documentcatalog: indicates the full-text index on which the full-text directory is created.
With change_tracking: Specifies whether the SQL Server maintains a list of all changes to the index data. Change tracking does not record data changes made through writetext and updatetext. He has several options
1. Manual: Specifies whether to use the SQL Server Agent to disseminate change tracking logs as planned or manually by users.
2. Auto: when data is modified in the associated table, SQL Server automatically updates the full-text index. The default value is auto.
3. off [, no population] specifies that the SQL server does not retain the list of changes to the index data. The no population option can be used only when ange_tracking is off. If no population is specified, SQL server does not fill the index after it is created. The index is populated only when you run the alter Fulltext index command using the start full or incremental population clause. If no population is specified, SQL Server will completely fill the index after it is created.
Of course, if you need to use full-text indexes, you need to start the full-text index service, which is the SQL Server Fulltext search service. Note that full-text indexes cannot be used for the SQL Express version.

The last step is how to perform full-text search. In the query type, we cannot use the like keyword for full-text search. The ins predicate should be used. Its first parameter is the column name for full-text search. Multiple columns can be specified, it must be separated and enclosed in parentheses. The second parameter is the text to be searched. The second parameter must be enclosed in single quotation marks. The queried text can be enclosed in quotation marks, you can also use wildcards and condition statements, such

Where contains (a. Content, '"China *" or "Wuhan "')
Condition statements can be represented by symbols, such as and, and can be replaced by &. For more information, see the help document of sqlserver.

The third parameter is the query language. If a column contains multiple languages, you can specify the search language.

In addition to the contains predicate, freetext can be used for full-text retrieval. However, the predicate is used to search for columns containing character-based data types. It seems that binary stream Columns cannot be searched, however, the help document also says that columns that support image are supported. However, the search accuracy of freetext is not as high as that of contains. We recommend that you use ins predicates.

 

this article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/firewolffeng/archive/2007/12/20/1956081.aspx