Example of a complete full-text index for SQLSERVER Database

A complete full-text index example of SQLSERVER database, using pubs database as an example. First, we will introduce how to create a full-text index using the system stored procedure: to create a full-text index for the title and notes columns of the pubs database, then, use the index to query whether the title or notes column contains datebase or c.

A complete full-text index example of SQL SERVER database, using pubs database as an example. First, we will introduce how to create a full-text index using the system stored procedure: to create a full-text index for the title and notes columns of the pubs database, then, use the index to query whether the title or notes column contains datebase or c.

A complete full-text index example of SQL SERVER database, using pubs database as an example.
The following describes how to create a full-text index using the system stored procedure:
Create a full-text index for the title and notes columns of the pubs database, Hong Kong space, and then use the index to query the name of a book with a datebase or computer string in the title or notes column:
Before that, the Microsoft Search Service must be installed on the Hong Kong virtual host to start the full-text Search service of 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