SQL Server Full-text retrieval overview. Full-text indexing and full-text retrieval are new functions of sqlserver7.0. it can Index character-type columns (such as varchar and text columns) in data, full-text search and full-text search are newly added to SQL server 7.0 by using indexes. it can be used to search for character-type columns (such as varchar and text columns) in data.
And full-text search and query through the index. Compared with full-text retrieval, SQL server's regular indexes have the following differences:
General index full-text index
Use create index or constraint definition to create and delete a full-text index stored procedure
Delete by deleting or executing the drop index statement
When inserting, modifying, or deleting data, SQL server can only store data through task scheduling or execution
Ability to automatically update the general index content to fill the full-text index
Each table can have multiple regular indexes. each table can have only one full-text index.
Indexes cannot be grouped into multiple full-text indexes in the same database.
Organize as a full-text Directory
Regular indexes are stored in database files. full-text indexes are stored in the file system.
To support full-text indexing, SQL server 7.0 adds some new stored procedures and transact-SQL statements.
This step is used (the name of the stored procedure called in each step is enclosed in brackets ):
(1) start the full-text processing function of the database (sp_fulltext_datebase );
(2) create a full-text Directory (sp_fulltext_catalog );
(3) register the table (sp_fulltext_table) that requires full-text indexing in the full-text directory );
(4) name of the full-text search column in the table (sp_fulltext_column)
(5) create a full-text index for the table (sp_fulltext_table );
(6) fill in the full-text index (sp_fulltext_catalog ).
Example:
Use pubs
Go
Exec sp_fulltext_database 'enable'
-- Create full-text index data elements for the titles table, where "create" is created, "activate" is activated, and "deactivate" is activated to disable the full-text index of the table.
It does not participate in full-text directory filling. drop is delete. in the create parameter, the full-text directory name and index column name are followed.
-- The following statement creates a full-text index data element for the titles table in the pubs database. The full-text directory for storing the data element is FT_pubs, and the unique index used is
UPKCL_titleidind (unique index created by the primary key constraint of 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 the columns that participate in the full-text index
Sp_fulltext_column 'title', 'title', 'add'
Sp_fulltext_column 'tidles ', 'note', 'add'
Server 7.0 is a new feature that allows you to index character columns (such as varchar and text columns) in data and implement full-text search through indexes...
