Using full-text indexing in SQL Server

Source: Internet
Author: User
Tags date sql server books sql
server| Index
Author:david Euler.


Have any questions, please contact me:

In general, using Full-text indexing in SQL Server is a 4-step process:

1). Install full Text search Full-text Indexing Service;

2. Create full text catalog Full-text index catalog for datasheet;

3. Perform full text catalog population operations (synchronize Full-text indexing with datasheet content);

4. Use full-text indexing for queries.

In order to keep the contents of the Full-text indexed database up-to-date when the content of the datasheet is updated, you can create the population automatic schedule of the full text catalog by step 5th.

1.sql server does not install the full Text Search service by default and will be available when installed. If the full Text Search service is installed, in SQL Server Enterprise Manager (Enterprise Manager), expand a database with a full text catalogs; Select a database, right-click the "new" There is a new full Text CataLog. and select any table, right-click, there will be a Full-text index table (full-text indexed tables) menu can be used. If the full text Search service is not installed, the menu is grayed out.

2. Create a full text catalog for the table. Suppose there is a table Mybbs in the database Coredb, which stores the ID of all posts in a forum, the title (title), the author (author), the contents (content), and so on. We select the table Mybbs, right-click, new full text Catalog, select content as the Full-text indexed field, and set up the whole text Catalog, named content.

3. By this time, only the full text catalog has been established and full-text indexing cannot be used. You need to align content in the Full-text catalog with the contents of the Database Index table, which is implemented through full population or incremental population: Expand the database in Enterprise Manager coredb, double-click Full-text Catalog, You will see a Full-text catalog named content, select right click, click Start Full Population or start incremental Population, and you can use the indexing feature.

4. Use indexing function:

SELECT * from Mybbs where Contains (content, ' windows ');

This statement queries the records that contain Windows strings in the Full-text index content of the Mybbs table.

5. In the future, when the index table in the database is updated, population synchronization is required, and the content of the Full-text query is the content of the new datasheet. So we can set SQL Server to automate population operations: Right-click Full text catalog,schedules,new catalog schedules named content, enter a name, select the Enabled option, You can set the frequency at which scheduled tasks are to be executed, set to start each time the SQL Server Agent is started, or set to execute once, or set to repeat execution, which can be set to run at a frequency of months, weeks, and days, and can be set up several hours a day. (This will automatically execute the SQL Server Agent,population synchronization schedule as soon as the service starts)

To summarize, you can start using Full-text indexing queries by installing the Full-text Indexing Service, creating a full-text indexed catalog of data tables, and synchronizing the Full-text indexing database. The dynamic Synchronization index database can be derived by creating an automatic population schedule of full text catalog.

Reference: SQL Server's own Help document SQL Server Books Online.

Full-text Catalogs and Indexes

Full-text Indexes

Full-text Search

Microsoft Search Service

Full-text Querying Support

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.