SQL Server full-text search

CreateCatalog

Now you need to create a full-text catalog. This directory seems to be a container that indexes all the full text. Therefore, if you want to search for the text of the three required tables, you need to generate three indexes (one index for each table) and put the three indexes in a catalog.
Tip: the complete SQL version is required for some information related to operations on the Microsoft website. For example, the "Storage folder" is not available in the Express version.

Don't worry. Next we will not manually create a catalog through the management interface. In the demo, only one catalog is created and two full-text indexes are put in it. In my personal experience, if an index involves a large amount of data, you should create a separate catalog for each index.

The following operation creates a catalog:

1. Program-SQL Server Management Interface-connect to the database to be operated
2. Click "new query"
3. Input:

Use dbname Go Exec sp_fulltext_database 'enable' Go Create Fulltext catalog catalogname Go

 

Description: dbname refers to the name of the database to be operated; catalogname refers to the name of catalog, which is defined as needed.

1. Execute the preceding SQL statement and enable Fulltext searching on the corresponding database to create the full-text directory.

Tip: If the directory is large, it is wise to put the directory on another hard disk or address. This will greatly improve the performance.

Create full-text index

A prerequisite for creating a full-text index is that the table must have such an index "unique, single-column, non-nullable Index ". In most cases, the primary key meets this requirement.

The actual requirements of SQL Server are as follows:

1. non-null indexes are required (primary keys all meet this requirement ).

2. The index must be used in a single column. The composite primary key does not meet this requirement. A work around und can create a new column, such as textid, int type, auto-increment, and index.

Tip: If your primary key is too long, I suggest creating a new int-Type Auto-increment column. For example, if the primary key is guid, the overhead for creating full-text indexes will be huge.

Create a full-text index

1. Ensure the conditions mentioned above; unique, single-column, non-nullable Index
2. Input SQL statement

Create Fulltext index onYourdatabase. DBO. yourtable ( Column_to_index Language 0x0 ) Key IndexYourindexOnYourcatalog With change_tracking auto

Note:

Yourdatabase. DBO. yourtable:User Database Name and description

Column_to_index: Columns with full-text content

Yourindex:Index in the table (that is, the index described above must meet the requirements of unique, single-column, and non-nullable index)

Yourcatalog: Catalog name created at the beginning of this article

Language: Tell SQL server that it can be any language (of course, you can also change it to Chinese or English through management studio. The specific language encoding is not described here)

Tip: If your table has a large amount of data, it may take some time to create a full-text index, or even 100% CPU usage for a period of time.

Use full-text search

Run the following SQL statement:

SELECT description FROM yourtable WHERE CONTAINS(description, '"NBA Playoff"')

Tip:

1. string format. The outermost side is single quotation marks, which are double quotation marks and the innermost part is the text to be searched.

2. If you want to blur it, use *, for example, '"NBA *" to search for all the texts starting with the NBA.

3. starting to blur is not supported. The author doesn't just ask, '"* ba *"' doesn't find anything. If you have any ideas, please kindly advise.

Some debugging methods

1. SQL Server Log: C:/program files/Microsoft SQL Server/mssql.1/MSSQL/log
2. Fulltext index log: sqlftxxxxxx. Log

Tip: the readability is very poor, but it is helpful to debug after learning about it.

Others

1. After SQL Server 2005, a table can have two or more full-text indexes.

2. Find the column in the table as the full-text search column select * From sys. fulltext_indexes

3. How to add a full-text index

exec sp_fulltext_column 'Tabname', 'Colname', 'add', '2057'

Note:

Tabname: name of a table with one or two segments. The table must already exist in the current database. The table must have a full-text index.

Colname: name of a column in qualified_table_name. This column must be a character or an image column. It cannot be a computed column.

2057: indicates the language, and code '000000' indicates UK English

Region settings	Region ID
Neutral	0
Simplified Chinese	0 × 0804
Traditional Chinese	0 × 0404
Dutch	0 × 0413
English	0 × 0809
American English	0 × 0409
French	0 × 040c
Germany	0 × 0407
Italy	0 × 0410
Japanese	0 × 0411
Korean	0 × 0412
Modern Spanish	0 × 0c0a
Normal Swedish	0 × 041d

 

Conclusion

Full-text search is easy to establish. Once created, quick response will surprise users and users!