Overview

• Full-Text Search lets users and applications run full-text queries against character-based data in tables with any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM.
• Each full-text index, indexes one or more columns from the table with a specific language.
• Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese.
• Full-text queries can include simple words and phrases or multiple forms of a word or phrase.
• A full-text query returns any documents that contain at least one match (also known as a hit).
• A match occurs when a target document contains all the terms specified in the full-text query, and meets any other search conditions, such as the distance between the matching terms.

Full-Text Search Queries

• After columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns.
• These queries can search for any of the following:
  • One or more specific words or phrases (simple term)
  • A word or a phrase where the words begin with specified text (prefix term)
  • Inflectional forms of a specific word (generation term)
  • A word or phrase close to another word or phrase (proximity term)
  • Synonymous forms of a specific word (thesaurus)
  • Words or phrases using weighted values (weighted term)
  • Full-text queries are not case-sensitive.
    • For example, searching for "Aluminum" or "aluminum" returns the same results.
  • Full-text queries use a small set of T-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE).
    • However, the search goals of a given business scenario influence the structure of the full-text queries.

Compare Full-Text Search queries to the LIKE predicate

• In contrast to full-text search, the LIKE T-SQL predicate works on character patterns only.
• Also, you cannot use the LIKE predicate to query formatted binary data.
  • Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data.
• A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Set up Full-Text Search

• There are two basic steps to set up full-text search:
  1. Create a full-text catalog.
  2. Create a full-text index on tables or indexed view you want to search.
• Each full-text index must belong to a full-text catalog.
  • You can create a separate text catalog for each full-text index, or you can associate multiple full-text indexes with a given catalog.
  • A full-text catalog is a virtual object and does not belong to any filegroup.
  • The catalog is a logical concept that refers to a group of full-text indexes.
• To create a full-text catalog named AdvWksDocFTCat:

CREATE FULLTEXT CATALOG [AdvWksDocFTCat];

• Before you can create a full-text index on the Document table, ensure that the table has a unique, single-column, non-nullable index.