Introduction to SQL Server Full-text search

Full-text indexing and Full-text search are new features of SQL Server 7.0 that enable you to cable character-type columns in your data, such as varchar, text, and other types of columns
, 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 '