Breaking Changes to Full-Text Search in SQL Server 2008 R2
Nota
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. |
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. .gif "Note") Note
SQL Server 2005 Transact-SQL DDL statements that specify full-text catalogs work correctly.
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |