A point worth repeating is that these extensions are consistent with SQL supported for full-text searches in Indexing Services 2.0. Furthermore, the SQL support for full-text searches follows the SQL-3 functional methodology for full-text syntax extensions.
The primary extension to Transact-SQL consists of the CONTAINS and FREETEXT predicates. These predicates are used to find column values that match special full-text search criteria.
In addition, the new CONTAINSTABLE() and FREETEXTTABLE() rowset-valued functions are supported. These functions can be specified (in the FROM clause) to return a two-column table, where one column uniquely identifies the rows that match the specified full-text search criteria and the other contains rank values showing the degree to which the rows match the criteria.
To be consistent with similar features in other products and to make the new predicates more extensible, functional notation has been chosen. The high-level syntax is:
In Microsoft SQL Server 7.0, the language always is implied by the locale of the database holding the data. The flexibility of the functional style of these two predicates allows easy, upward-compatible future extension for a third parameter to designate the language to be used for the query.
These predicates can be used with the character family of data types: char, varchar, text, nchar, nvarchar, and ntext. Often, document formats stored in columns defined with the binary family of data types cannot be indexed or searched. Such support will be a candidate for a future release of SQL Server.