Full-text index description in mssqlserver

I. A Brief Introduction to full-text indexing
You can use indexes to quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table.
Indexes provide pointers to the data values of the specified columns stored in the table, and sort these pointers according to the specified sorting order.
The database uses an index in a similar way to a book Directory: searches for an index to find a specific value, and then follows the pointer to the row containing the value.

II. Full-text index operations
1. Check whether full-text index is enabled
The full-text index feature of each database created in SQL2005 is enabled by default.
You can use this statement to check whether it is enabled.

The code is as follows:	Copy code
SELECT databasePropertyex ('xland ', 'isfulltextenabled ')

0 is not enabled, and 1 is enabled.
2. Activate and disable full-text indexing
To disable full-text indexing, you must note that the full-text directory already exists, and any associated information of the data table with this directory will be deleted.

The code is as follows:	Copy code
Exec sp_fulltext_database @ action = 'disable' 'Enable' is activated.

3. Create a full-text index

The code is as follows:	Copy code
Use xland Go Create fulltext index on dbo. mytable -- create a full-text index on a table (Title language English) -- use full-text index for a column Key index Pk_mytable -- index name (usually the table name is preceded by a Pk) On maincatalog With change_tracking off, -- disable the tracking feature No population -- no initialization filling

 
You can change Eglish to simplified Chinese.
Available

The code is as follows:	Copy code
Select name alias from master... syslanguages

 
Languages supported in the query system
Modify and delete full-text indexes

The code is as follows:	Copy code
Drop fulltext index on dbo. mytable

 

3. Full-text directory operations
1. Create a full-text Directory
When creating a full-text directory, the full-text index must be in the enable state.
The path to the full-text directory must be valid.

The code is as follows:	Copy code
Use xland Go Exec sp_fulltext_database @ action = 'enable' Go Create fulltext catalog maincatalog In path 'C: fulltext'

Alter and drop

The code is as follows:	Copy code
Drop fulltext catalog maincatalog

There is no much difference from others
2. Fill in the full-text Directory
This is a background process. If the table is large, it is prompted to be successful, but the background is still being filled.

The code is as follows:	Copy code
Alter fulltext index on dbo. mytable Start full population   Or Use xland Go Exec sp_fulltext_table @ tabname = 'dbo. Mytable ', @ Action = 'start _ full'

3. View Results

The code is as follows:	Copy code
Select title from mytable where contains (title, 'full ')

IV. Full-text query syntax
1. contains: Exact match

The code is as follows:	Copy code
Where contains (*, '"my name is"') -- exact match of the phrase. Note that there must be double quotation marks. Where contains (*, '"my" or "name is"') -- or relationship

And 'and not' and'
I will not say much about the adjacent words and weights.
2. freetext: fuzzy match (you can enter swim to match swam) is the same as the contains syntax.
3. containstable
This function returns a table
This table contains two columns
One is the key (or the master key) and the other is the rank (the matching degree between the returned rows and the search result is greater and more accurate)

The code is as follows:	Copy code
Select title from mytable p Join containstable (mytable, title, 'xland') ct On p. id = ct. [key]

 
4. freetexttable is similar to containstable.

Attachment: example of creating a full-text index using the old syntax

The code is as follows:

Copy code

Use xland Go -- Check whether the database pubs supports full-text indexing. If not -- Use sp_fulltext_database to enable this function. If (select databaseproperty ('xland ', 'isfulltextenabled') = 0 Execute sp_fulltext_database 'enable' -- Create the full-text directory FT_xland Execute sp_fulltext_catalog 'FT _ xland ', 'Create' -- Create full-text index data element for the mytable table Execute sp_fulltext_table 'mytable', 'create', 'FT _ xland ', 'PK _ mytable' -- the last one is the primary key name. -- Set full-text index column name Execute sp_fulltext_column 'mytable', 'title', 'add' -- Create full-text index -- Activate: Enables full-text retrieval of a table, that is, registering the table in the full-text directory. Execute sp_fulltext_table 'mytable', 'activate' -- Fill in the full-text Index Directory Execute sp_fulltext_catalog 'FT _ xland ', 'start _ full' Go -- Check full-text directory filling While fulltextcatalogproperty ('FT _ xland ', 'populatestatus') <> 0 Begin -- If the full-text directory is being filled, wait 30 seconds and check again. Waitfor delay '0: 0: 30' End -- After the full-text directory is filled, you can use full-text directory retrieval. Select * from mytable where CONTAINS (title, 'xland ')

: