RE: Searching Words and Phrases in a Rich Text Field

SSCommitted

Points: 1669

July 25, 2007 at 7:16 am

SQL Server 2005 documentation on the "CREATE FULL-TEXT CATALOG" statement shows the following:

The default full-text filegroup is the primary filegroup for the database. We recommend that full-text catalogs be put on a secondary filegroup. This lets you do filegroup backup-and-restore operations of full-text catalogs independent of the data and log files. Additionally, by putting the full-text catalogs in a secondary filegroup, you can restore an online full-text catalog.

Internal system tables are also used for storing full-text index information (from http://msdn2.microsoft.com/en-us/library/ms142541.aspx):

Internal Tables

Full-Text Search uses the following internal tables:

fulltext_index_map stores mappings between full-text key columns and internal document IDs that uniquely identify documents.
fulltext_catalog_freelist stores unused document IDs.

Obviously, Microsoft will not tell us exactly how full-text catalog/indexes are created and used, but the above quotes show that SQL Server 2005 integrates full-text catalogs/indexes into the database better than SQL Server 2000.

However, the exact integration of full-text catalogs/indexes is not important. The authors code mimics full-text functionality, which is available when you install SQL Server. Personally, I don't want to "re-invent the wheel" unless I have something to gain (performance, ease of maintenance, etc.).