SQL full-text index

Basic knowledge

1
The. SQL Server 7 desktop version does not have full text retrieval.

2
A table can only have one full text search.

3
. The retrieved table must have a unique index for a single column.

4
. Full text indexes are stored in the file system, not in the database.

5
. The process of updating a full text index is more time-consuming than that of a regular index, and can be updated immediately by the database system as a regular index.

6
. The full text index is included in the full text directory (
Full
-
Text
Catalog), each database can contain one or more directories, but a directory cannot belong to multiple databases.

7
. Full text search can only be created on a real table, not a view, system table, or temporary table.

8
. Some noise words are ignored during full text search, such as English A,,
And
, Chinese
'
And
'
,
'
Yes
'
And so on.

9
. If the query contains noise words, an error is thrown, and these noise words should be removed in the application.

Start the full text search service.
Method A: Open the Support Services folder in the Enterprise Manager.
Full
-
Text
Right-click Search and choose start from the context menu.
Method B: select Microsoft search from the services drop-down list of SQL Server Service Manager, and click Start
/
Continue
Button.
Method C: Use the Net start MSSEARCH command line.
Use the full text search wizard (
Full
-
Text
Indexing wizard ).
Step 1. Select the database to be retrieved. In the Tools menu, select
Full
-
Text
Indexing to enter the welcome screen, click
Next
.
Step 2: select the table to be retrieved and click
Next
.
Step3. select a unique index and click
Next
.
Step 4. Select the indexed column and click
Add
, Which is displayed in the right column. Click
Next
.
Step 5. Select a directory (select an existing directory or create a new directory) and click
Next
.
Step 6. Select or create population schedule (optional) and click
Next
.
Step7. click Finish.
Use SQL
-
DMO (using VB as an example)
Step 1. select Microsoft sqldmo object library from the project reference.
Step 2: Create an sqlserver object.
Dim objsql
As
New sqldmo. sqlserver
Objsql. Connect "localhost", "sa ",""
Step create a new directory and add it to the database directory to be indexed.
Dim objcatalog
As
New sqldmo. fulltextcatalog

'
Make pubs a database for full text search
Objsql. Databases ("pubs"). enablefulltextcatalogs

'
Create a new directory
Objcatalog. Name
=
"Ftcpubstest"

'
Add a new directory to the directory set
Objsql. Databases ("pubs"). fulltextcatalogs. Add objcatalog
Step 4. Create a full text index on the table.
Dim objtable as new sqldmo. Table

'
Table to be indexed

Set
Objtable
=
Objsql. Databases ("pubs"). Tables ("Authors ")

'
Specify the directory name and unique index name
Objtable. fulltextcatalogname = "ftcpubstest"
Objtable. uniqueindexforfulltext = "upkcl_auidind"
Objtable. fulltextindex = true

'
Column to be indexed
Objtable. Columns ("au_lname"). fulltextindex
=
True
Objtable. Columns ("au_fname"). fulltextindex
=
True

'
Activate the full text index of the table
Objtable. fulltextindexactive = true

Step 5. Start the full text directory
Objcatalog. Start sqldmofulltext_full
Use stored procedures
Step1. make pubs a database for full text search
Use pubs
Go
Sp_fulltext_database
'
Enable
'

Step 2. Create a new directory
Sp_fulltext_catalog
'
Ftcpubstest
'
,
'
Create
'

Step3. specify the table to be indexed
Sp_fulltext_table
'
Authors
'
,
'
Create
'
,
'
Ftcpubstest
'
,
'
Upkcl_auidind
'

Step 4. Specify the column to be indexed
Sp_fulltext_column
'
Authors
'
,
'
Au_lname
'
,
'
Add
'

Sp_fulltext_column
'
Authors
'
,
'
Au_fname
'
,
'
Add
'

Step 5. Activate the full text index of the table
Sp_fulltext_table
'
Authors
'
,
'
Activate
'

Step 6. Start the full text directory
Sp_fulltext_catalog
'
Ftcpubstest
'
,
'
Start_full
'

Contains syntax
We usually use contains in the WHERE clause, like this: Select * From table_name where contains (fulltext_column,
'
Search Contents
'
).

Let's take an example to learn about it. Suppose there is a table students, where the address is a full text retrieval column.
1. query student addresses in Beijing
Select student_id, student_name
From students
Where contains (address,
'
Beijing
'
)
Remark: Beijing is a word that must be enclosed in single quotes.

2. query student addresses in Hebei Province
Select student_id, student_name
From students
Where contains (address,
'
"Heibei province"
'
)
Remark: Hebei province is a phrase that must be enclosed in double quotation marks.

3. query student addresses in Hebei province or Beijing
Select student_id, student_name
From students
Where contains (address,
'
"Heibei province"
Or
Beijing
'
)
Remark: You can specify logical operators (including and, and not, or ).

4. the query has
'
Nanjing Road
'
Address
Select student_id, student_name
From students
Where contains (address,
'
Nanjing near Road
'
)
Remark: The above query will return the include
'
Nanjing Road
'
,
'
Nanjing East Road
'
,
'
Nanjing West Road
'
.
A near B indicates that A is near B.

5. query
'
Lake
'
Address starting
Select student_id, student_name
From students
Where contains (address,
'
"Hu
*
"
'
)
Remark: The above query will return the include
'
Hubei
'
,
'
Hunan
'
.
Remember: *, not %.

6. Weighted queries
Select student_id, student_name
From students
Where contains (address,
'
Isabout (city weight (.
8
), County Wright (.
4
))
'
)
Remark: isabout is the keyword of this query. Weight specifies a value ranging from 0 ~ The number between 1, similar to the coefficient (I understand ). Indicates that different conditions have different focuses.

7. multi-state query of words
Select student_id, student_name
From students
Where contains (address,
'
Formsof (inflectional, Street)
'
)
Remark: The query returns include
'
Street
'
,
'
Streets
'
.
For a verb, different tenses are returned, such as dry, dry, dried, drying, and so on.

All of the above examples use English, not Chinese because some query methods do not support Chinese, and my computer is an English System

Payment: Question 1 in full-text search:

5. The process of updating a full text index is more time-consuming than that of a regular index, and can be updated immediately by the database system as a regular index.
Can be updated immediately
9. If the query contains noise words, an error will be thrown, and these noise words should be removed in the application.
No. Noise word will be filtered out during the query, and errors will only occur when all the queried content is noise words.

Containstable syntax
We usually use containstable in the from clause, just like this: Select * From table_name, containtable (fulltext_table, fulltext_column,
'
Search Condition
'
) Where .......

The containstable query method is almost the same as that of contains. Containstable returns a table that meets the query conditions. In SQL statements, we can use it as a normal table.

Let's look at an example to compare the differences between the two tables.
Select ft_tbl.student_name, ft_tbl.student_score, key_tbl.rank
From report as ft_tbl inner join
Containstable (student, address,

'
Isabout (city weight (.
8
), County Wright (.
4
))
'
) As key_tbl
On ft_tbl.student_id = key_tbl. [Key]
Order by key_tbl.rank
The table package returned by containstable contains two special columns: Key and rank.
In the first part, we stressed that the table to be indexed in full text must have a unique index. The unique index column becomes the key in the returned table. We usually use it as a condition for table join.

When searching for some websites, numbers indicating the degree of matching appear in the results. Rank is similar to this. Its value ranges from 0 ~ Between 1000, the matching degree between each row and the query condition is higher, and the rank value is large. Generally, it is sorted in descending order of rank.
Freetext syntax
Freetext is similar to contains, but it does not have high accuracy. In contains, there are many requirements for writing query conditions, but freetext does not. It can be any word, phrase, or sentence. See the following example:
Select categoryname
From categories
Where freetext (description,
'
Sweetest candy bread
And
Dry meat
'
)

Freetexttable syntax
Like containstable, freetexttable returns a table with key and rank. Example:
Select ft_tbl.categoryname,
Ft_tbl.description,
Key_tbl.rank
From categories as ft_tbl inner join
Freetexttable (categories, description,

'
Sweetest candy bread
And
Dry meat
'
) As key_tbl
On ft_tbl.categoryid = key_tbl. [Key]
Use full text search in ASP
Dim CNN
Dim rs
Dim strsql

Strsql = "select book_name "&_
"From books "&_
"Where contains (description,
'
"
&
Request ("search_condition ")
&
"
'
)"

Set CNN = server. Createobject ("ADODB. Connection ")
Set rs = server. Createobject ("ADODB. recordset ")

CNN. Open "provider = sqloledb; datasource =.; initial catalog = books; user id = sa; pasword = ;"
Rs. Open strsql, CNN

The above example is very simple, just for illustration. As long as you have mastered the syntax of contains and containstable, the usage is the same as that of the general ADO query.