How SQL Server adds a full-text index

A technique called full-text indexing is provided in SQL Server, which can greatly improve the speed of searching data from long strings, rather than using inefficient fuzzy queries like this. The following concise description of how to use Sql2008 full-text index One, check the service inside the service with the Full-text Word exists and open
If a service with Full-text is not present, verify that the Sqlserverfulltextsearch is installed

--Check whether the database PS2 supports full-text indexing if not supported
--use sp_fulltext_database to open the feature

if (databaseproperty ('PS2','isfulltextenabled'))=0'enable '           

Second, new full-text catalogA full-text catalog is used to store full-text indexes Iii. Defining a full-text index for a table Four, click Next, follow the prompts to select 1. Confirm next 2. Select a unique index, usually the primary key 3. Select the full-text index column you want to create, and select Chinese if the column is in Chinese. If it is in English, choose English4. Select the index Update method, you can automatically update, and later the data volume can be set to add a full-text index of the plan 5. Select Full-text catalog five, full-text indexed SQL query keyword after establishing a full-text index can use SQL statements to query, mainly with three keywords CONTAINS, FREETEXT, CONTAINSTABLE and FREETEXTTABLE1. CONTAINS search for exact or ambiguous matches of a single word and phrase, the content to search must be a meaningful word, such as "Apple", "Building Hall", not a few meaningless words, such as "Ah Di is", "son" such words, even if like can be queried out, But the full-text index may not be indexed to such meaningless words, not to be found.

1. SELECT *
2. FROM dbo. Business
3. WHERE CONTAINS (Address,' travel ')

Implementation function: Query the address column in the business table that contains "travel" rows
View more: Http://msdn.microsoft.com/zh-cn/library/ms187787.aspx2. FREETEXT and contains are similar, the difference is that it will first query the word Advanced word segmentation and then in the query match

1. A.SELECT *
2. . fromdbo. Business
3. where FREETEXT (Address,' travelling with infants ')

View more: Http://msdn.microsoft.com/zh-cn/library/ms176078.aspx3.CONTAINSTABLE is almost the same as CONTAINS in query mode. But ContainsTable returns a table that matches the query criteria, and in the SQL statement we can use it as a normal table, and use the CONTAINSTABLE query to return a correlation rank value (rank) and full-text key (key) for each row.

1. SELECT  *  
2. from    business as ft_tbl   
3.          inner join containstable (business, *, 
4.         as  Key_tbl on ft_tbl. Businessid = key_tbl. [key]  
5. order by key_tbl. Rank desc  

ISABOUT is the keyword for this query, and weight specifies a number, similar to a factor, between 0~1. Indicates that different conditions have different emphasis. The table returned by CONTAINSTABLE contains a special two columns: Key,rank. A table that is full-text indexed must have a unique index. This unique index column becomes KEY in the returned table. We usually use it as a condition for table joins. RANK is similar to the number of matches that appear in the results when searching for certain sites. Its value is between 0~1000, which identifies how much each row matches the query criteria, and the higher the rank value, usually in descending order of rank. View more: Http://msdn.microsoft.com/zh-cn/library/ms189760.aspx4. Freetexttable is almost the same as FREETEXT in query mode. But Freetexttable returns a table that matches the query criteria, and in the SQL statement we can use it as a normal table, and use the FREETEXT query to return a correlation rank value (rank) and full-text key (key) for each row.

2. SELECT *,
4. Businessid,
6. Address
8. From business as ft_tbl
10. INNER JOIN freetexttable (Business, Address, ' ISABOUT (with Infant travel WEIGHT (. 8), Chishui WEIGHT (. 4)) ')
12. As key_tbl on ft_tbl. Businessid = Key_tbl. [KEY]
14. ORDER by Key_tbl. RANK DESC

View MORE: Http://msdn.microsoft.com/zh-cn/library/ms177652.aspx

--activate, which is the full-text retrieval capability of the active table, that is, registering the table in the full-text catalog

'prosearch ','activate'    

--Populate full-text index catalogs

'prosearchcatalog ','start_full'    

--Query whether full-text indexing is complete (0: finished; 1: Building)

FullTextCatalogProperty ('prosearchcatalog','populatestatus ')     

Third, introduce some related operations of the full-text index
To view the configuration of a full-text search:

----- View Test table which fields are configured for full-text indexing  

Deleting a full-text index must be the reverse step of creating it:

--Revoke full text search on test -- Revoke full text catalog Ft_testdata  

How SQL Server adds a full-text index