Example of SQL _Server full-text index usage

Enable SQL full-text search to improve query performance. This article describes how to use SQL full-text index. For more information about how to create a full-text index of an SQL Server database, see the following example) start the full-text processing function of the database (sp_fulltext_databas

Enable SQL full-text search to improve query performance. This article describes how to use SQL full-text index. For more information about how to create a full-text index for SQL SERVER databases, see the following example) start the full-text processing function of the database (sp_fulltext_databas

Enable SQL full-text search to improve query performance. This article describes how to use SQL full-text index. For more information, see

This article demonstrates the complete full-text index of the SQL SERVER database. The pubs database is used 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.

The Code is as follows:

Use 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 'note', '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