Introduction to SQL Server Full-text search

server| full-text Indexing and Full-text search is a new feature of SQL Server 7.0 that enables you to cable character type columns (such as varchar, text, and other types of columns) in your data
, and the Full-text search query is implemented through the index. The difference between SQL Server regular indexes and Full-text search is as follows:

General indexed Full-text Indexing
Create and delete using full-text indexing stored procedures by using CREATE INDEX or constraint definitions
Delete by deleting or executing the DROP INDEX statement

When data is inserted, modified, or deleted, SQL Server can only be scheduled or executed by a task that has been stored
Ability to automatically update regular index content to populate Full-text indexing

Each table can establish multiple regular indexes, each table can have only one Full-text index
Indexes cannot group multiple full-text indexes in the same database can be
To organize as a Full-text catalog
Regular indexes stored in the database file The Full-text index is stored in the file system

To support Full-text indexing operations, SQL Server 7.0 adds new stored procedures and Transact-SQL statements that use these stored procedures to create full-text indexed
This step is (the name of the stored procedure called in parentheses for each step):

(1) to start the Full-text processing function of the database (sp_fulltext_datebase);
(2) Establishment of Full-text catalogs (sp_fulltext_catalog);
(3) Register the table (sp_fulltext_table) that requires Full-text indexing in the Full-text catalog;
(4) Indicate the name of the column in the table that requires Full-text search (sp_fulltext_column)
(5) Create a Full-text index (sp_fulltext_table) for the table;
(6) Populate Full-text indexing (sp_fulltext_catalog).

Cases:
Use pubs
Go
EXEC sp_fulltext_database ' Enable '
--Create a full-text indexed data element for the titles table, where create is established, activate is active, deactivate to turn off the activation state of the table Full-text index, so
It no longer participates in the Full-text catalog population, the drop is deleted, the Create parameter is followed by the Full-text catalog name and the indexed column name.
--The following statement creates a full-text indexed data element for the titles table in the pubs database, the Full-text catalog for the data element is ft_pubs, and the unique index used is
Upkcl_titleidind (the only index established by the PRIMARY KEY constraint for the title_id column in the title table)
sp_fulltext_table titles, ' Create ', ' ft_pubs ', ' upkcl_titledind '

--Activate it
Sp_fulltext_table titles, ' Activate '

--Specify columns to participate in Full-text indexing
Sp_fulltext_column ' titles ', ' title ', ' Add '
Sp_fulltext_column ' titles ', ' Notes ', ' add '

The following is a complete example:
--Start SQL Server's Full-text Search service before executing the script, the Microsoft Search service
Use pubs--Open the database
Go
--Check whether pubs support Full-text indexing, or use Sp_fulltext_datebase to open this feature if Full-text indexing is not supported
if (select Databaseproperty (' pubs ', ' isfulltextenables ')) =0
Execute sp_fulltext_database ' Enable '
--Create a Full-text catalog Ft_pubs
Execute sp_fulltext_catalog ' ft_pubs ', ' create '
--Create a full-text indexed data element for the titles table
Execute sp_fulltext_table ' titles ', ' Ft_pubs ', ' upkcl_titleidind '
--Set full-text index column names
Execute sp_fulltext_column ' titles ', ' title ', ' Add '
Execute sp_fulltext_column ' titles ', ' Notes ', ' add '
--Establish Full-text indexing
Execute sp_fulltext_table ' ft_pubs ', ' activate '
--Populating Full-text index catalogs
Execute sp_fulltext_catalog ' ft_pubs ', ' start_full '
Go
--Check Full-text catalog fill
While FullTextCatalogProperty ("Ft_pubs ', ' populatestatus ') <>0
BEGIN
--If the Full-text catalog is in a filled state, wait 30 seconds before testing again
WAITFOR DELAY ' 0:0:30 '
End
--After Full-text catalog population is complete, use Full-text catalog to retrieve

--The name of the book that contains a database or computer string in the title column or notes column
SELECT Title
From title
where containts (title, ' database ')
or contains (notes, ' database ')
or contains (title, ' computer ')
or contains (notes, ' computer ')