Full-text index of an SQL Server database using the pubs database as an Example

The following describes how to create a full-text index using the system stored procedure:

1) Start the full-text processing function of the database (sp_fulltext_database)

2) create a full-text directory (sp_fulltext_catalog)

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

4) Name of the column that requires full-text indexing in the table (sp_fulltext_column)

5) create a full-text index for the table (sp_fulltext_table)

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

--------- ********* Example ********-------------

Create a full-text index for the title and notes columns of the pubs database, and then use the index to query the name of the book containing the Datebase or computer string in the title column or notes column:

Before that, you need to install the Microsoft Search Service and start the full-text search service for SQL Server.

User pubs -- open the database

Go

-- Check whether the database pubs supports full-text indexing. If not

-- Use sp_fulltext_database to enable this function.

If (select databaseproperty ('pubs', 'isfulltextenabled ') = 0

Execute sp_fulltext_database 'enable'

-- Create the full-text directory ft_pubs

Execute sp_fulltext_catalog 'ft _ pubs ', 'create'

-- Create full-text index data element for the title table

Execute sp_fulltext_table 'title', 'create', 'ft _ pubs', 'upkcl _ titleidind'

-- Set full-text index column name

Execute sp_fulltext_column 'title', 'title', 'add'

Execute sp_fulltext_column 'title', 'note', 'add'

-- Create full-text index

-- Activate: Enables full-text retrieval of a table, that is, registering the table in the full-text directory.

Execute sp_fulltext_table 'title', 'activate'

-- Fill in the full-text INDEX DIRECTORY

Execute sp_fulltext_catalog 'ft _ pubs ', 'start _ full'

Go

-- Check full-text directory Filling

While fulltextcatalogproperty ('ft _ pubs', 'populatestatus') <> 0

Begin

-- If the full-text directory is being filled, wait 30 seconds and check again.

Waitfor delay '0: 0: 30'

End

-- After the full-text directory is filled, you can use full-text directory retrieval.

Select title

Form

Where contains (title, 'database ')

Or contains (title, 'computer ')

Or contains (notes, 'database ')

Or contains (notes, 'database ')

　

'-------------- The following describes the full-text operating system stored procedures.

Procedure name: sp_fulltext_service

Execution permission: serveradmin or System Administrator

Usage: set full-text search attributes

Procedure name: sp_fulltext_catalog

Execution permission: db_owner and higher role members

For use: Create and delete a full-text directory, and start or stop the index operation of a full-text directory.

Procedure name: sp_fulltext_database

Execution permission: Member of db_owner role

For use: Initialize the full-text index or delete all full-text directories in the database

Procedure name: sp_fulltext_table

Execution permission: db_ddladnmin or db_owner role member

For use: Identify a table as a full-text index table or a non-full-text index table.

Procedure name: sp_fulltext_column

Execution permission: Member of the db_ddladnmin role

Usage: indicates the columns in a full-text index table. If the full-text index is left or not