Providing Full text search on sqlServer table.

INTRODUCTION
In this article I will show you to provide full text search on sqlserver table. Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server. Although the concept of full text search is not new in SQL Server, there are considerable enhancements in performance and manageability of SQL Server.

EXAMPLE
The below articles demonstrates the how to provide full text search on sqlserver table. Let us take example of implementing full text search on Employee table in AdventureWorks database.

Step1:-
Select the table under the database for which you want to implement the full text search.

Step2:
Right click the table on which you want to implement the full text indexing and navigate to “Full-text” index and click on define “Full Text Index……”

Step3:-
On clicking the “Define Full Text indexing……” full text indexing wizard opens.

Step4:-
Click on the next button to continue. Select the unique index from the dropdown.

Step5:-
On clicking the next button the columns in the table appears. Select the columns from the list you want to apply the full text search and click on the next button.

Step6:-
Select “automatically” radio button from the list and click next.

Step7:-
Specify the catalog name in the “name” textbox and click on the next button.

Step7:-
Specify the catalog name in the “name” textbox and click on the next button.

Step8:-
Keep on clicking the next button until you reached finishing screen as shown below. Click on the finish button to complete the process of full text search indexing.

Until now, we have set-up the full text search catalogues and indexes. Let us run the some queries for the full-text search. There are four types of predicates used in SQL Server  for running the Full text search queries. A predicate is an expression that evaluates to TRUE, FALSE or UNKNOWN. A predicate is used in a where condition in T-SQL statement.

1.    FREETEXT
2.    FREETEXTTABLE
3.    CONTAINS
4.    CONTAINSTABLE

Let us see each one of them individually with its usage.

FREETEXT
Freetext is the simplest form of predicates in the full text search. It searches for the words that give the similar meaning and not the exact wording of the word. This kind of predicate is used where we go to query a document as a whole for a word in it. The freetext not only returns the exact wording, but also the nearest meanings attached to it. Listing 1 shows the usage of the free text. To understand listing 1, the user should have a basic understanding of T-SQL queries. Let us also see the usage of Freetext with a help of an example. Listing 2 gives an example for the usage of FREETEXT.

Example 1
FREETEXT ( { column_name | (column_list) | * } , ‘freetext_string’ [ , LANGUAGE language_term ] )

Example 2
SELECT CellularPhone FROM CART WHERE FREETEXT (CellularPhone, ‘1111′)

Example 3
SELECT CELLULARPHONE FROM CART WHERE CELLULARPHONE LIKE ‘%1111%’

FREETEXTTABLE
Freetexttable returns a collection of one or more rows as a table. The columns in the rows are selected based on the string given as the predicate. Freetexttable is similar to freetext except the former can return tables and the latter one can return columns. Listing 4 shows the usage of Freetexttable.

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

CONTAINS
As the name suggests, Contains actually works by verifying if a word or phrase is contained in the columns. It can search for a word, prefix of a word, synonym of a word, word formed from another word. We need to specify the language in case the languages of the words are not of the same language. Listing 5 shows the simple usage of contains column.

Example 5
SELECT [Name], [Address] FROM Customer WHERE AGE > 30 AND CONTAINS([Name], ‘Pranav’);

CONTAINSTABLE
Contains and Containstable are similar to freetext and freetexttable. The former one returns columns and the latter one returns the table.

Conclusion
Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. There are many enhancements made to the full-text search. In sql server it uses the MSSearch functionality. The concept of ranking is also included in this version. Ranking can give a score about the relevance of the search made. In this article we tried to expose all the basic functionalities of full-text search.