Full Text Queries : CONTAINSTABLE and FREETEXTTABLE functions

This is third in the series of post on full text search.This is a continuation and you can visit my earlier posts to know what is full text search and how to use full text predicates for querying.

In this post we will be discussing on full text functions.For the sake of easy reference I am again adding the content related to types of full text queries below from my second post.

1. Simple Term : Searching for a particular word or phrase
2. Prefix Term : searching for prefix word or phrase
3. Proximity Term: Looks for words or phrases close to each other
4. Generation Term : Looks for inflectional forms i.e. if  the  word is a verb the search will look for  various tenses of word .E.g  if you search for  “Start”  you will get results for Start ,Started,Starting etc.Similarly if word you are looking for is a Noun you will get singular and plural forms of it.
5. Thesaurus : Looks for synonyms of a particular word based on thesaurus.E.g if you search for Start, search will look for Start ,Begin etc.
6. Weighted Term: Looks for various search terms based on the weights associated with them

We saw in the earlier post how to use all the above types (except the 6th ) using predicates.

We can use all the above types using full text functions and also the 6th type using which you can rank your results based on the relevance.You can also give weights to different search terms and rank them based on the weights.

Whenever you use full text functions the result table returns you a key column which has the unique key on which full text index is defined and a Rank column which specifies the relative rank of the result.Like predicates you have two  full text functions viz. CONTAINSTABLE (similar to CONTAINS predicate)  and FREETEXTTABLE (similar to FREETEXT predicate).

Again the difference between the two functions is that CONTAINSTABLE allows you to run various types of queries  separately whereas FREETEXTTABLE by default does a search on various forms i.e. it includes inflectional as well as Thesaurus search.

Lets see some examples of using full text functions.

Query to return all rows having words “Communication”,”Chemicals”  or “Jewellers” in either column EmployeeProfile or EmployeeJobSector with highest preference given to “Communication” and lowest to “Jewellers”.

SELECT * FROM
EmployeeProfile EP
INNER JOIN
CONTAINSTABLE(EmployeeProfile, EmployeeJobSector,
        'ISABOUT ("Communications" WEIGHT(1),"Chemicals" WEIGHT(0.6)
         ,"Jewellers" WEIGHT(0.3)) ') AS KEY_TBL
          ON EP.EmployeeId = KEY_TBL.[KEY]
          ORDER BY RANK

This is an example of weight based ranking.You use ISABOUT and WEIGHT terms to  achieve this kind of ranking.Also value of weight should be between 0.0 and 1.0.

Below is the result of above query.

Query to return all rows having inflectional forms of word “Teach” i.e. Teach,Taught , Teaching etc in either column EmployeeProfile or EmployeeJobSector .

SELECT * FROM
EmployeeProfile EP
INNER JOIN
CONTAINSTABLE(EmployeeProfile, EmployeeResume,
        'FORMSOF(INFLECTIONAL,teach) ') AS KEY_TBL
          ON EP.EmployeeId = KEY_TBL.[KEY]
          ORDER BY RANK DESC

Below are the query results.It seems ranking algorithm here gives higher rank to present and past tense.Not sure why but looks like thats how it works.

Query to return all rows having word   Extensive and Experience near to each other in column EmployeeProfile .

SELECT * FROM
EmployeeProfile EP
INNER JOIN
CONTAINSTABLE(EmployeeProfile,
        'NEAR(extensive,experience)') AS KEY_TBL
          ON EP.EmployeeId = KEY_TBL.[KEY]
          ORDER BY RANK desc

Notice below that relevance or ranking of results is based on proximity of the words.

Finally below is the query using FREETEXTTABLE to show how different forms of word can be searched in the same query.

Query to return all rows having word   Start (and all the derived forms) in column EmployeeProfile .

Note the results search for both inflectional as well as thesaurus forms.

This along with the earlier two post concludes the configuration and querying part of full text searching.In next post I would be addressing some advanced concepts in full text searching.Till then Happy Querying :-).

For further reading on this and related topics, you can refer to this exhaustive course on pluralsight.