Use full-text index in SQL Server

Generally, the full-text index in SQL Server takes four steps:

1 ). Install full Text Search full-text index service;

2 ). Create full for the data table Text Catalog full-text index directory;

3 ). Full Text The population operation of catalog (synchronize the full text index with the data table content );

4 ). Use full-text index for query.

To keep the full-text index database content up-to-date when the data table content is updated, you can create full Text The population of catalog automatically operates schedule.

1 . Full is not installed on SQL Server by default. Text Search Service, which can be used only after installation. If full is installed Text Search Service, then in SQL Server Enterprise Manager, expand a database, there will be a full Text Catalogs; select a database and Right-click a database and choose "New" from the shortcut menu. Full   Text Catalog. and select any table. Right click and a full - Text   Index   Table (Full-text index table) menu can be used. If full Text If the search service is not installed, the menu is gray.

2 . Create full for the table Text Catalog. Assume that the database coredb contains a table named mybbs, which stores the ID, title, author, and content of all posts in a forum. Right-click the mybbs table and choose new. Full   Text Catalog, select content as the full-text index field, and create a new full Text Catalog, named content.

3 At this time, only the full Text Catalog, does not support full-text indexing. Make sure that the content in the full-text directory is consistent with that in the database index table. This step is achieved through full population or incremental population: expand the database coredb in the Enterprise Manager, and double-click "full ". - Text Catalog, you will see a full-text directory named content, right-click, and click Start Full Population or start incremental population, you can use the index function.

4 . Use the index function:

Select   *   From Mybbs Where   Contains (Content, ' "Windows" ' );

This statement queries records containing 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. The full-text query content is the content in the new data table. Therefore, we can set SQL Server to automatically perform the population operation: Right-click full with the name of content Text Catalog, schedules, new catalog schedules, enter a name, select the enabled option, you can set the execution frequency of scheduled tasks, you can set to start each time you start the SQL Server Agent, you can also set to run once, you can also set the repetition interval to monthly, weekly, or daily. You can set the repetition interval to several hours per day. (As long as the SQL Server Agent is started in the service, the population synchronization plan will be automatically executed)

To sum up, install the full-text index service to create a full-text index directory for a data table. After synchronizing the full-text index database, you can start to use full-text index query. You can create full Text the automatic population schedule of catalog automatically synchronizes the index database.