5

Just want to know about the processes happening in full text indexing. I know by defining the full text indexing for a table, it will index all the words saved in that table.

But i didn't get the use of creating a catalog for a full text index. And how an index related to catalog. Simply what the full text catalog will do in full text indexing.

Improve this question

2 Answers 2

Reset to default
4

From BOL:

After columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns. These queries can search for any of the following:

  • One or more specific words or phrases (simple term)
  • A word or a phrase where the words begin with specified text (prefix term)
  • Inflectional forms of a specific word (generation term)
  • A word or phrase close to another word or phrase (proximity term)
  • Synonymous forms of a specific word (thesaurus)
  • Words or phrases using weighted values (weighted term)

A full-text catalog is a logical concept that refers to a group of full-text indexes. Catalogs makes it easier for maintaining full text indexes.

Improve this answer
2
  • 2
    I still don't get why it need for, and when I need to put 2 indexes in one catalog vs create separate catalog for each FT index
    – Mike Keskinov
    Commented Feb 16, 2016 at 16:11
  • @MikeKeskinov I'm not sure this is still useful for you, but my answer has some details. In short, put an index for a table with "millions of rows" in its own catalog. Other indices should be grouped by similar 'update characteristics', e.g. number of changes, or frequent changes during similar times.
    – Kenny Evitt
    Commented Oct 13, 2020 at 21:48
2

The following was copied (and lightly edited) from this answer to this Stack Overflow question:

Taken from:

Recommendations from SQL Server 2008 Books Online: We recommend associating tables with the same update characteristics (such as small number of changes versus large number of changes, or tables that change frequently during a particular time of day) together under the same full-text catalog.

By setting up full-text catalog population schedules, full-text indexes stay synchronous with the tables without adversely affecting the resource usage of the database server during periods of high database activity. When you assign a table to a full-text catalog, consider the following guidelines:

Always select the smallest unique index available for your full-text unique key. (A 4-byte, integer-based index is optimal.) This reduces the resources required by Microsoft Search service in the file system significantly. If the primary key is large (over 100 bytes), consider choosing another unique index in the table (or creating another unique index) as the full-text unique key. Otherwise, if the full-text unique key size exceeds the maximum size allowed (900 bytes), full-text population will not be able to proceed.

If you are indexing a table that has millions of rows, assign the table to its own full-text catalog.

Consider the amount of changes occurring in the tables being full-text indexed, as well as the total number of rows. If the total number of rows being changed, together with the numbers of rows in the table present during the last full-text population, represents millions of rows, assign the table to its own full-text catalog.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.