Chapter 6 Querying Full-Text Data

Question 1

What is full-text search?

Answer 1

Full-text search enables you to run full-text queries against character-based data in SQL Server tables.

Question 2

What are the prerequisites before you start using full-text predicates and functions?

Answer 2

You must create full-text indexes inside full-text catalogs. The full-text indexes are on character-based columns in your database.

Question 3

What does full-text search allow you to search for?

Answer 3

(1) Simple Terms - One or more specific words or phrases (2) Prefix Terms - Terms the words or phrases begin with (3) Generation Terms - Inflectional forms of the words (4) Proximity Terms - words or phrases close to another word or phrase (5) Thesaurus Terms - synonyms of a word (6) Weighted Terms - words or phrases that use custom weight values (7) Statistical Semantic Search - Key phrases in a document (8) Similar Documents - similarity defined by key phrases.

Question 4

On what types of columns can you create full-text indexes?

Answer 4

CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, IMAGE, XML, VARBINARY(MAX)

Question 5

What do you need in order to use full-text search?

Answer 5

(1) Check whether the Full-Text Search is installed: SELECT SERVERPROPERTY(‘IsFullTextInstalled’) (2) Full-text indexes must be created on character data or document (binary/image/xml) columns (3) For documents, you need appropriate filters (ifilters) that allow you to extract the textual information and remove the formatting from the documents. You can check which filters are installed: EXEC sys.sp_help_fulltext_system_components ‘filter’. (4) After you install the filter pack, register the filters: EXEC sys.sp_fulltext_service ‘load_os_resources’, 1; (5) You may need to restart SQL Server.

Question 6

What is a word breaker?

Answer 6

Identifies individual words or tokens. Tokens are inserted in a full-text index in compressed format. Word breakers are language specific.

Question 7

What is a stemmer?

Answer 7

Stemmers generate inflectional forms of a word based on the rules of a language. Stemmers are language specific.

Question 8

What are stoplists?

Answer 8

You can prevent indexing “noise” words by creating stoplists of stopwords. This helps to prevent bloating a full-text index with words that don’t help your searches.

You can check current stoplists and stopwords using:

SELECT stoplist_id, names
FROM sys.fulltext_stoplists;

SELECT stoplist_id, stopword, language
FROM sys.fulltext_stopwords;

Question 9

What is a thesaurus file?

Answer 9

Full-text queries can search not only for words you provide in a query, they can search for synonyms as well. SQL Server finds synonyms in thesaurus files. Each language has an associated XML thesaurus file. You can edit the thesaurus files and configure the following elements: (1) diacritics_sensitive - 0 accent insensitive/1 accent sensitive, (2) expansion - expansion word “author” to “writer” in order to link the two, (3) replacement - “Windows 2008” could be a replacement for “Win 2k8”. After you edit the thesaurus file for a specific language, you must load it with the following stored procedure call: EXEC sys.sp_fulltext_load_thesaurus_file 1033; (1033 is the language id - US-EN).

Question 10

Can full-text queries search on document properties?

Answer 10

Yes. Which properties can be searched depends on the document filter. You can create a search property list to define searchable properties for you documents.

Question 11

Where are full-text indexes stored?

Answer 11

Full-text indexes are stored in full-text catalogs. A full-text catalog is a virtual object, a container for full-text indexes. As a virtual object it does not belong to any filegroup.

Question 12

Can you store indexes from the same full-text catalog to different filegroups?

Answer 12

Yes. A full-text catalog is a virtual object only; full-text indexes are physical objects. You can store each full-text index from the same catalog to a different file group.

Question 13

What is statistical semantic search?

Answer 13

It gives you deeper insight into documents by extracting and indexing statistically relevant key phrases. Full-text search uses these key phrases to identify and index documents that are similar or related.

Question 14

Which database do you have to install in order to enable the Semantic Search feature?

Answer 14

You need the semanticsdb database.

Question 15

How can you create synonyms for the words searched?

Answer 15

You can add synonyms by editing the thesaurus file.

Question 16

What are the two predicates that can be used with full-text indexes?

Answer 16

CONTAINS and FREETEXT

Question 17

What is the CONTAINS predicate?

Answer 17

You can use the CONTAINS predicate in your WHERE clause to search for the following: (1) words or phrases in text, (2) exact or fuzzy matches, (3) inflectional forms of a word, (4) text in which a search word is close to another word, (5) synonyms of a searched word, (6) a prefix of a word or phrase. Also, you can add custom weights to the words you’re searching for.

Question 18

What is the form of CONTAINS to find an exact match - a simple term?

Answer 18

WHERE CONTAINS(FTcolumn, ‘SearchWord1’)

Question 19

What is the form of CONTAINS that uses logical operators?

Answer 19

WHERE CONTAINS(FTcolumn, ‘SearchWord1 OR SearchWord2’). You can also use AND and AND NOT logical operators and change the order of eval using parenthesis.

Question 20

What is the form of CONTAINS that finds an exact match for a phrase?

Answer 20

WHERE CONTAINS(FTcolumn, ‘SearchWord1 SearchWord2’)

Question 21

What is the form of CONTAINS that finds a word that starts with a certain prefix?

Answer 21

WHERE CONTAINS(FTcolumn, ‘SearchWord1*’)

Question 22

What is the form of CONTAINS where the FTcolumn contains SearchWord1 and SearchWord2? The simplest custom proximity term. Search for the occurrence of both words no matter what the distance and order of the terms.

Answer 22

WHERE CONTAINS(FTcolumn, ‘NEAR(SearchWord1, Searchword2)’) - Note that the order of the search terms is not important.

Question 23

What is the form of CONTAINS where the FTcolumn contains SearchWord1 and SearchWord2 and specifies how many nonsearch terms can maximally be between the searched terms?

Answer 23

WHERE CONTAINS(FTcolumn, ‘NEAR((SearchWord1, SearchWord2), distance)’) - Note that order of the search terms is not important.

Question 24

What is the form of CONTAINS where the FTcolumn contains SearchWord1 and SeachWord2 and specifies the max distance between the two search terms, but also takes a flag to indicate if the order of the search terms is important?

Answer 24

WHERE CONTAINS(FTcolumn, ‘NEAR((SearchWord1, SearchWord2), distance, flag)’); The flag can take TRUE or FALSE; the default is FALSE. If flag is TRUE, then the order of the search terms is important.

Question 25

What is the form of CONTAINS where any inflectional forms of SearchWord1 are found?

Answer 25

WHERE CONTAINS(FTcolumn, ‘FORMSOF(INFLECTIONAL, SearchWord1)’)

Question 26

What is the form of CONTAINS where synonyms for SearchWord1 are found?

Answer 26

WHERE CONTAINS(FTcolumn, ‘FORMSOF(THESAURUS, SearchWord1)’) - Note that the synonyms will come from the thesaurus file.

Question 27

What is the form of CONTAINS where the terms are weighted?

Answer 27

WHERE CONTAINS(FTcolumn, ‘ISABOUT(SearchWord1 weight(w1), SearchWord2 weight(w2))’) - Note that the weights have an influence on the rank of the documents returned. CONTAINS does not rank, but the weighted form is useful for the CONTAINSTABLE function.

Question 28

What is the form of CONTAINS allowing you to search a document’s properties?

Answer 28

WHERE CONTAINS(PROPERTY(FTcolumn, ‘PropertyName’), ‘SearchWord1’) - Note that you’re searching for rows with documents that have the property PropertyName that contains the value SearchWord1.

Question 29

What is the FREETEXT predicate?

Answer 29

The FREETEXT predicate is less specific and thus returns more rows than the CONTAINS predicate. With FREETEXT, the engine performs word breaking of the search phrase, generates inflectional forms (does the stemming), and identifies a list of expansions or replacements for the words in the searched term with words from the thesaurus., e.g. FREETEXT(FTcolumn, ‘SearchWord1 SearchWord2’) - here, you’re searching for rows where FTcolumn includes any inflectional forms, synonyms, etc.

Question 30

Which predicate CONTAINS or FREETEXT is more specific?

Answer 30

You use the CONTAINS predicate for more specific searches. FREETEXT can be used for more general searches.

Question 31

What are the two full-text table-valued functions?

Answer 31

CONTAINSTABLE and FREETEXTTABLE

Question 32

What are the three semantic search table-valued functions?

Answer 32

SEMANTICKEYPHRASETABLE (semantic key phrase table), SEMANTICSIMILARITYTDETAILSTABLE (semantic similarity details table), and SEMANTICSIMILARITYTABLE (semantic similarity table).

Question 33

What do CONTAINSTABLE and FREETEXTTABLE return?

Answer 33

They return two columns: KEY and RANK. The KEY column is the unique key from the index used in the KEY INDEX clause of the CREATE FULLTEXT INDEX statement. RANK returns an ordinal value between 0 and 1000. This is the rank value. It tells you how well a row matches your search criteria. There is a different rank calculation between the two functions since the latter does not support the majority of the parameters that the first one does like proximity and weight.

Question 34

What is the form of the CONTAINSTABLE function?

Answer 34

CONTAINSTABLE ( table , { column_name | ( column_list ) | * } , ‘ ‘
[ , LANGUAGE language_term]
[ , top_n_by_rank ] )

Question 35

What is the form of the FREETEXTTABLE function?

Answer 35

FREETEXTTABLE (table , { column_name | (column_list) | * }
, ‘freetext_string’
[ , LANGUAGE language_term ]
[ , top_n_by_rank ] )

Question 36

For the CONTAINSTABLE and FREETEXTTABLE functions, what does the top_n_by_rank parameter do?

Answer 36

The top_n_by_rank parameter is an integer that specifies that only the n rows with the highest rank should be returned in the rowset.

Question 37

For the CONTAINSTABLE and FREETEXTTABLE functions, what does the language_term parameter do?

Answer 37

This is the language SQL Server uses for word breaking, stemming, thesaurus, and stopword removal as part of the query. If no value is specified, the column’s full-text language is used. This parameter could be useful when you store documents of different languages in a single column. When you query such a column, language_term can increase the quality of matches. You specify it as an integer, representing the LCID or local identifier.

Question 38

What is the form of the SEMANTICKEYPHRASETABLE function?

Answer 38

SEMANTICKEYPHRASETABLE
(
   table,
   { column | (column_list) | * }
   [ , source_key ]
)

Question 39

What is the SEMANTICKEYPHRASETABLE function?

Answer 39

This function returns a table with key phrases associated with the full-text indexed column from the column_list.