Create a full-text index on the database Test

Create a full-text index favorites on the database Test
/*
Create a full-text index on the database Test
*/
Use test
Create Table poofly (ID int not null, name varchar (10 ))
Go
/* Create a unique index for full-text indexing */
Create unique clustered index un_ky1 on poofly (ID)
/* Create a full-text directory */
Create Fulltext catalog FT1 as default
/* C create a full-text index */
Create Fulltext index on poofly (name) Key Index un_ky1 on FT1
/* Modify the full-text directory */
Alter Fulltext catalog FT1 rebuild
/* Delete the full-text directory ft (the full-text index cannot be deleted when it is included )*/
Drop Fulltext catalog ft
/* View all full-text directories of the database */
Select * From SYS. fulltext_catalogs
/*
Fulltext_catalog_id name path is_default is_accent_sensitivity_on data_space_id file_id principal_id is_importing
------------------- Begin-certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 test null 0 1 null 1 0
11 FT1 null 1 1 null 1 0
*/
/* View all tables that use full-text indexes */
Exec sp_help_fulltext_tables
/*
Table_owner table_name fulltext_key_index_name fulltext_key_colid fulltext_index_active fulltext_catalog_name
Accept-Certificate----------------- ----------------------- accept -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBO poofly un_ky1 1 1 FT1
*/
/*
Create a full-text index on the database Test
*/
Use test
Create Table poofly (ID int not null, name varchar (10 ))
Go
/* Create a unique index for full-text indexing */
Create unique clustered index un_ky1 on poofly (ID)
/* Create a full-text directory */
Create Fulltext catalog FT1 as default
/* C create a full-text index */
Create Fulltext index on poofly (name) Key Index un_ky1 on FT1
/* Modify the full-text directory */
Alter Fulltext catalog FT1 rebuild
/* Delete the full-text directory ft (the full-text index cannot be deleted when it is included )*/
Drop Fulltext catalog ft
---------------------------------------------------
-- Use the contains keyword for full-text indexing

-- 1. prefix search
Select name from TB where contains (name, '"China *"')
/* -- Note that * The returned result is chinax chinay chinaname China.
-- The returned prefix is China's name.
-- If "" is not used, the system will match the ins (name, 'China') with China */

-- 2. search using acronyms
Select name from TB where contains (name, 'formsof (inflectional, "foot ")')
/* The result may be foot feet (all vertices have different forms of nouns in the singular and plural forms )*/

-- 3. Weighted word search
Select value from TB where contains (value, 'isabout (performance weight (. 8 ))')
/* The full value uses a number ranging from 0 to 1 to indicate the importance of each word */

-- 4. Near word search
Select * from TB where contains (document, 'a near B ')
/* The result is the document with the word "A" approaching the word "B ".
Can be written as contains (document, 'a ~ B ')
*/
-- 5. boolean logical search
Select * from TB where contains (name, '"A" and "B "')
/* Returns the rows that contain both A and B words.
Of course, the and keyword here is changed to or, and not.
*/
--------------------------------------------------
-- You can also use rreetext for fuzzy search
-- Any input text Full Text Index automatically identifies important words and constructs a query
Use test
Go
Select * from TB where freetext (wendang, 'zhubajie Chi Xi Gua! ')