The primary extension to Transact-SQL consists of the CONTAINS and FREETEXT predicates.
CONTAINS is the predicate used to determine if values in a column contain certain words or phrases. The CONTAINS predicate supports complex syntax to search character-based columns. More complex conditions are specified by individual operands with the Boolean operators AND, OR, and AND NOT. The queries can be broadened by the INFLECTIONAL generation term that directs the query to match plural and singular forms of nouns and the various tenses of verbs. The proximity term NEAR is used when words or phrases being searched are required to be near one another. To return ranked results, the query uses the weighted term ISABOUT. Wildcard searches, searching to match words or phrases that begin with a specified text followed by an asterisk (*) are supported in full-text CONTAINS queries.
FREETEXT is the predicate used to search columns that match the meaning and not the exact wording of the words in the search condition. Full-text searches using FREETEXT are less precise than full-text queries using CONTAINS. The CML application does not use the FREETEXT predicate in any queries.