Create an SQL full-text index

1.

------------- Enable full-text index and create full-text INDEX DIRECTORY
Exec sp_fulltext_database 'enable'
-- Exec sp_fulltext_catalog 'gf _ ft ', 'drop'
Exec sp_fulltext_catalog 'gf _ ft ', 'create'

------------- Create full-text index indexed columns for the news table as title, abstract, text

Exec sp_fulltext_table 'product', 'create', 'gf _ ft ', 'pk _ product'
Exec sp_fulltext_column 'product', 'productname ', 'add'

------------- Activate the index
Exec sp_fulltext_table 'product', 'activate'
Exec sp_fulltext_table 'product', 'start _ full'

-- Check full-text directory Filling
While fulltextcatalogproperty ('gf _ ft ', 'populatestatus') <> 0
Begin

-- If the full-text directory is being filled, wait 5 seconds and check again.
Waitfor delay '0: 0: 5'
End

Exec sp_fulltext_catalog 'gf _ ft ', 'start _ full'

-- Check full-text directory Filling
While fulltextcatalogproperty ('gf _ ft ', 'populatestatus') <> 0
Begin

-- If the full-text directory is being filled, wait 5 seconds and check again.
Waitfor delay '0: 0: 5'
End

-------------- Test ------------------
Select * from product where contains (productname, 'mead Johnson ')

----------------------- Uninstall ------------------

Exec sp_fulltext_table 'product', 'activate'
Exec sp_fulltext_column 'product', 'productname ', 'drop'
Exec sp_fulltext_table 'product', 'drop'
Exec sp_fulltext_catalog 'gf _ ft ', 'stop'
Exec sp_fulltext_catalog 'gf _ ft ', 'drop'

 

 

2.

First, let's take a look at how full-text indexing is created and used.

Create a full-text index:

In ms SQL Server 2005, full-text indexing is a separate service item, which is started by default, but does not allow the database to enable full-text indexing. If you want to create a full-text index in a database, you must first enable full-text indexing for the database.
Enabling method:
1. Choose database> Properties> General> enable full-text index.
2. Run "SP _..." in the query. If it is not enabled, Run "create index directory". A prompt is displayed.

After the index is enabled, you can create an index directory.
Execute the following statement in the query:
Create Fulltext catalog index directory name

After the command is executed, a directory name with the same name will be displayed in the installation directory of SQL Server. The path is mssql.1/MSSQL/ftdata/INDEX DIRECTORY name.

Each table can only have one full-text index, which is stored in the specified index directory. You can create a full-text index by using the wizard or by using SQL.
Basic Syntax:
Create Fulltext index on tablename
(Index column 1, index Column 2 ...)
Key Index indexname on index directory name

Indexname refers to the existing unique index name based on the specified table, rather than the unique index column name. If the index does not exist, you need to create a unique index first.

Full-text index:

Mainly use contains and freetext for query

Assume that a table music has created a full-text index for the field memo, the statement to query all records containing Jay Chou or Jay is:
Select * from music where contains (memo, '"Jay Chou" or "Jay "')

You can also use the matching mode to combine the inclusion conditions, or use the and connection conditions.