• Article

Breaking Changes to Full-Text Search in SQL Server 2008 R2

Notes

For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.

This topic describes breaking changes in full-text search. These changes might break applications, scripts, or functionalities that are based on earlier versions of SQL Server. You might encounter these issues when you upgrade. For more information, see Using Upgrade Advisor to Prepare for Upgrades.

Breaking Changes

The following breaking changes apply to Full-Text Search in SQL Server 2008.

Feature

Scenario

SQL Server 2005

SQL Server 2008

CONTAINSTABLE with user-defined types (UDTs)

The full-text key is a SQL Server user-defined type, for example, MyType = char(1).

The returned key is of the type assigned to the user-defined type.

In the example, this would be char(1).

The returned key is of the user-defined type. In the example, this would be MyType.

top_n_by_rank parameter (of the CONTAINSTABLE and FREETEXTTABLE Transact-SQL statements)

top_n_by_rank queries using 0 as the parameter.

Fails with an error message stating that you must use a value greater than zero.

Succeeds, returning zero rows.

CONTAINSTABLE and ItemCount

Delete rows from base table before it pushes changes to MSSearch.

CONTAINSTABLE returns ghost record. ItemCount is not changed.

CONTAINSTABLE does not return any ghost records.

FREETEXT or FREETEXTTABLE

Searching for a exact phrase.

If the search string (freetext_string) is enclosed within double quotation marks, stemming and thesaurus matches are not performed, and the string returns only exact matches as if it were a phrase search. For example, specifying "fast ships" in a FREETEXT predicate would return only rows that contained "fast ships". The phase "ship your package fast" would not be returned.

Phrase searches are no longer allowed by FREETEXT and FREETEXTTABLE, which perform stemming and thesaurus matches regardless of whether single or double quotation marks enclose the search string. For example, searching on "fast ships" would return both "fast ships" and "ship your package fast".

To search on a phrase, use CONTAINS or CONTAINSTABLE.

ItemCount

Table contain null documents or type columns.

In addition to indexed documents, documents that are null or that have null types are counted in the ItemCount value.

Only indexed documents are counted in the ItemCount value.

Catalog ItemCount

Blob column with a NULL extension.

It is counted in ItemCount of catalog

It is not counted in ItemCount of catalog.

UniqueKeyCount

Querying a unique key count from a catalog, for example, two tables (table1 and table2) each with three words: word1, word2, and word3.

UniqueKeyCount = 9. The following table summarizes how this value is attained:

table1 = 3

EOF for full-text index of table1 = 1

table2 = 3

EOF for full-text index of table2 = 1

full-text catalog = 1

For each table, UniqueKeyCount is the number of distinct keywords + 1 (0xFF). This does NOT treat same words in > 1 doc as new unique key.

For a catalog, UniqueKeyCount is the sum of UniqueKeyCount of each of the tables under the catalog. Identical words from different tables are treated as unique keys. In this case the unique key count is 8.

precompute rank server-level option

Performance optimization of FREETEXTTABLE queries.

When the option is set to 1, FREETEXTTABLE queries specified with top_n_by_rank use precomputed rank data stored in the full-text catalogs.

Is not supported.

sp_fulltext_pendingchanges when updating key column

Update the full-text key column on one row of a 2-row table, and run sp_fulltext_pendingchanges.

Both rows appear.

Only one row appears.

Inline functions

Inline functions with a full-text operator

Return an error message.

Return the relevant rows.

sp_fulltext_database

Enable or disable full-text search by using sp_fulltext_database.

No results are returned for full-text queries. If full-text is disabled for the database, full-text operations are not allowed.

Returns results to full-text queries, and full-text operations allowed, even if full-text is disabled for the database.

Locale-specific stop words

Queries inlocale-specific variants of a parent language, such as Belgian French and Canadian French.

Queries inlocale-specific variants are processed by the components (word breakers, stemmers, and stop words) of their parent language. For example, the French (France) components are used to parse French (Belgium).

You must add stop words explicitly for each locale identifier (LCID). For example, you would need to specify an LCID for Belgium, Canada, and France.

Thesaurus stemming process

Using thesaurus and Inflectional forms (stemming).

A thesaurus word is automatically stemmed after its expansion.

If you want the stemmed form in the expansion, you need to explicitly add the stemmed form.

Full-text catalog path and filegroup

Working with full-text catalogs.

Each full-text catalog has a physical path and belongs to a filegroup. It is treated as a database file.

A full-text catalog is a virtual object and does not belong to any filegroup. A full-text catalog is a logical concept that refers to a group of full-text indexes.

NoteNote
SQL Server 2005 Transact-SQL DDL statements that specify full-text catalogs work correctly.

sys.fulltext_catalogs

Using the path, data_space_id, and file_id of this catalog view.

These columns return a specific value.

These columns return NULL because the full-text catalog is no longer located in the file system.

sys.sysfulltextcatalogs

Using the path column of this deprecated system table.

Returns the file system path of the full-text catalog.

Returns NULL because the full-text catalog is no longer located in the file system.

sp_help_fulltext_catalogs

sp_help_fulltext_catalogs_cursor

Using the PATH column of these deprecated stored procedures.

Returns the file system path of the full-text catalog.

Returns NULL because the full-text catalog is no longer located in the file system.

sp_help_fulltext_catalog_components

Using sp_help_fulltext_catalog_components of this stored procedure.

Returns a list of all components (filters, word-breakers, and protocol handlers), used for all full-text catalogs in the current database.

Returns empty rows.

DATABASEPROPERTY and DATABASEPROPERTYEX

Using the IsFullTextEnabled property.

The IsFullTextEnabled setting indicates whether full-text search is enabled in a given database.

The value of this column has no effect. User databases are always enabled for full-text search.