SQL Server Topic One: index (bottom)

First, this time the content is full-text index, and in fact, not much relationship with the previous

The function and structure of the two indexes are different, the structure of ordinary index is mainly based on B + Tree and hash index, which can be used to find the exact data in the field, such as finding a record of a field value equal to a given value, a=10 this kind of query, so it is suitable for numeric field and short text field. The full-text index is used to retrieve whether the field contains or does not contain the specified keyword, a bit like a search engine function, its internal index structure is the same as the search engine inverted index structure, the principle is that the text in the field of Word segmentation, and then for each occurrence of the word record an index entry, This index item holds all the information about the record that has occurred in that word, meaning that the word is found in the field of the record after it has been located in the index. It is therefore suitable for finding with large text fields. Large pieces of the reason is not suitable for ordinary indexing, the main reason is that the ordinary index to the search conditions can only be precisely matched, and the text content in big fields, and usually do not perform exact text matching query on this field, but more is based on the keyword full-text search query, such as you look up an article information, Instead of typing the entire article into a query (if you have an entire article), you'll just type in some keywords. The full-text index is suitable for this kind of query demand.

The following example turns from: http://blog.csdn.net/bloglife/article/details/2283072

Full-Text Indexing

A complete example of full-text indexing of SQL Server databases. (Take the pubs database as an example)

First, describe the steps to create a full-text index using system stored procedures:
1) Start the full-text processing function of the database (sp_fulltext_database)
2) Create a full-text catalog (sp_fulltext_catalog)
3) Register a table in the full-text catalog that requires full-text indexing (sp_fulltext_table)
4) indicates the column name in the table that requires full-text indexing (sp_fulltext_column)
5) Create a full-text index for the table (sp_fulltext_table)
6) Populate full-text catalogs (sp_fulltext_catalog)

example, to create a full-text index on the title and notes columns of the pubs database, and then use the index to query the title column or the notes column for a book name that contains a datebase or computer string:
Before that, you need to install the Microsoftsearch service to start the SQL Server full-Text search service

User Pubs--Open database
Go
--Check whether the database pubs supports full-text indexing, and if not, use sp_fulltext_database to open the feature
if (select Databaseproperty (' pubs ', ' isfulltextenabled ')) =0
Execute sp_fulltext_database ' Enable '
--Create full-text Catalogs Ft_pubs
Execute sp_fulltext_catalog ' ft_pubs ', ' create '
--Creating full-text index data elements for the title table
Execute sp_fulltext_table ' title ', ' Create ', ' ft_pubs ', ' upkcl_titleidind '
--Set the full-text index column name
Execute sp_fulltext_column ' title ', ' title ', ' Add '
Execute sp_fulltext_column ' title ', ' Notes ', ' add '
--Full-text indexing, activate, is the full-text retrieval capability of the active table, that is, registering the table in the full-text catalog
Execute sp_fulltext_table ' title ', ' Activate '
--Populate full-text index catalogs
Execute sp_fulltext_catalog ' ft_pubs ', ' start_full '
Go
--Check Full-text catalog fill condition
While FullTextCatalogProperty (' ft_pubs ', ' populatestatus ') <>0
Begin
--If the full-text catalog is in a populated state, wait 30 seconds and then detect again
WAITFOR DELAY ' 0:0:30 '
End

Third, full-text catalogs can be retrieved using full-text catalogs once the population is complete

SELECT * FROM title
where CONTAINS (title, ' database ')
or CONTAINS (title, ' computer ')
or CONTAINS (notes, ' database ')
or CONTAINS (notes, ' database ')

Four, the following describes the full-text operation of the system stored procedure class

Procedure name: Sp_fulltext_service
Execute permissions: serveradmin or system administrator
Role: Setting full-Text Search properties

Procedure name: sp_fulltext_catalog
Execute permissions: DB_Owner and higher role members
Role: Create and delete a full-text catalog, start or stop an index operation on a full-text catalog

Procedure name: sp_fulltext_database
Execute permissions: db_owner role member
Role: Initialize full-text indexing or delete all full-text catalogs in the database

Procedure name: sp_fulltext_table
Execute permissions: db_ddladnmin or db_owner role member
Role: Identify a table as a full-text indexed table or a non-full-text indexed table

Procedure name: Sp_fulltext_column
Execute permissions: Db_ddladnmin role members
Function: Indicates which columns in a full-text indexed table should or exits the full-text index

SQL Server Topic One: index (bottom)