SQL full-text index creation and query instances

This article describes how to create a full-text index and query the created full-text index.

This article describes how to create a full-text index and query the created full-text index.

Create full-text index

Before full-text retrieval, you must first create and fill the full-text index. To support full-text indexing, SQL Server 7.0 adds stored procedures and Transact-SQL statements. The procedure for creating a full-text index using these stored procedures is as follows (the name of the called stored procedure is in parentheses ):

1. Start the full-text processing function of the database (sp_fulltext _

Database );;

2. Create a full-text search directory (sp_fulltext_catalog );

3. register the table (sp_fulltext_table) that requires full-text indexing in the full-text search directory );

4. Name of the column (sp_fulltext _

Column );;

5. Create a full-text index for the table (sp_fulltext_table );;

6. Fill in the full-text search directory (sp_fulltext_catalog ).

The following example shows how to create a full-text index. In this example, create a full-text index for the Title column and the Notes column in the Test database Book table.

Use test // open the database

// Enable full-text indexing to enable SQL Server services

The Code is as follows:
Execute sp_fulltext_database 'enable'

// Create the full-text search directory ft_test

The Code is as follows:
Execute sp_fulltext_catalog 'ft _ test', 'create'

Create full-text index data element for the Title column. pk_title is the unique index created by the primary key in the Book table. This parameter is required.

The Code is as follows:
Execute sp_fulltext_table 'book', 'create', 'ft _ test', 'pk _ title'

// Set the full-text index column name

The Code is as follows:
Execute sp_fulltext_column 'book', 'title', 'add' Execute sp_fulltext_column 'book', 'note', 'add'

// Create a full-text index

The Code is as follows:
Execute sp_fulltext_table 'book', 'activate'

// Fill in the full-text INDEX DIRECTORY

The Code is as follows:
Execute sp_fulltext_catalog 'ft _ test', 'start _ full'

So far, the full-text index has been created.

References from other users
-- 1. Check whether the database northwind enables full-text index SELECT * FROM sys. databases
USE NorthWind
-- 2. Create a full-text directory

The Code is as follows:
Create fulltext catalog [employee_fulltext] WITH accent_sensitialog = OFF AS DEFAULT

-- 3. Specify a unique index

The Code is as follows:
Create fulltext index on [dbo]. [Employees] Key index [PK_Employees] ON ([employee_fulltext]) WITH (CHANGE_TRACKING AUTO)

-- 4. Add full-text index Columns

The Code is as follows:
Alter fulltext index on [dbo]. [Employees] ADD ([Address]) Go Alter fulltext index on [dbo]. [Employees] ADD ([LastName]) Go Alter fulltext index on [dbo]. [Employees] ADD ([FirstName]) Go

-- 5. Set to available

The Code is as follows:
Alter fulltext index on [dbo]. [Employees] ENABLE Go

SQL Server 2000 provides the following full-text search statements: CONTAINS and FREETEXT. The CONTAINS statement is used to search for a word or phrase in all or specified columns of a table. The prefix of a word or phrase is similar to that of a word; A derived word of a word; a repeated word.

The syntax format of the CONTAINS statement is:

The Code is as follows:
CONTAINS ({column | *}), _ Condition>)

Column is a search column. When "*" is used, it indicates that all full-text index columns in the table are searched. Contains_search _

Condition describes the search content of the CONTAINS statement. The syntax format is:

The Code is as follows:
{ | | | | } [{AND | and not | OR} }] [... N]

The simple_term and prefix_term parameters are briefly described below:

Simple_term is the word or phrase searched by the CONTAINS statement. When searching for a phrase, double quotation marks must be used as the delimiter. The format is:

{'Word' | "phrase "}

Prefix_term indicates the prefix of the word or phrase searched by the CONTAINS statement. The format is:

{"Word *" | "phrase *"}

For example, the following statement retrieves the name of a Book containing the "database" or "computer" string and Its annotation information in the Title column and Notes column of the Book table:

The Code is as follows:
Title, notes From book Where contains (tilte, 'database') or contains (notes, 'database ') Or contains (title, 'computer ') or contains (notes, 'computer ')

The FREETEXT statement is used to search for a free text string in all or specified columns of a table and return data rows matching the string. Therefore, the FREETEXT statement is also called a free full-text query.

The syntax format of the FREETEXT statement is: FREETEXT ({column | *}, 'freetext _ string ')

Column is the column to be searched. If "*" is used, all full-text index columns in the table are searched. The Freetext_string parameter indicates the searched free text format string.

For example, the following statement uses FREETEXT to search data rows in the Book table that contain the "Successful Life" string:

The Code is as follows:
Select title, notes From book Where freetext (*, 'successful life ')