Visit: https://zonixsoft.com (our Official Website)

Definition :

Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server.

Why Full Text Search?

Let us assume that we have huge amounts of data in the database and we are having some search functionality involved in User Interface (UI). The UI for the database is a website open to the internet so response time is crucial. Now if the administrator of the website wants to search all the customer names which have a specific pattern, then he or she queries the database system as shown in Listing 1.

 

Listing 1:

SELECT [NAME] FROM [CUSTOMER] WHERE [NAME] LIKE ‘%PATTERN%’The query in Listing 1 is fine for getting the result. But in real time this may not suffice the actual requirement. Let us see why the query in Listing 1 may not yield us actual results.

 

1. In case of a large amount of data, the like query works very slowly. It may take minutes to return the result. This is true especially for unstructured text data.

 

2. If I want to query my data on formatted binary data [format], I cannot use a Like query.

 

So to summarize, the need to of the hour is for a search functionality that is fast, efficient and can also be run on unstructured text data with the support of search on different data types like formatted binary. Full text search qualifies all these and in fact has many advantages.

 

 

Full-Text Indexing Architecture :

You might be a bit surprised to learn that SQL Server doesn’t handle its own full-text indexing tasks. Any version of Windows that SQL Server will run on includes an operating system component named the Microsoft Search Service. This service provides indexing and searching capabilities to a variety of applications, including SQL Server, Exchange, and SharePoint.

SQL Server uses an interface component, the SQL Server Handler, to communicate with the Microsoft Search Service. The Handler extracts data from SQL Server tables that have been enabled for full-text searching and passes it to the search service for indexing. Another component, the full-text OLE DB provider, gets invoked by SQL Server when you actually perform a full-text search. The provider takes the portion of the search that needs to be satisfied by the full-text index and passes it off to the Search Service for evaluation.

You need to be aware of one consequence of this architecture: because the full-text indexes are not in your SQL Server database, they can’t be backed up from within SQL Server. Instead, you need to backup the disk files created by the Search Service. You’ll find these files located under Program FilesMicrosoft SQL ServerMSSQLFTDATA.

 

 

Implementing Full Text search :

Let us discuss the steps for implementing full-text search in SQL Server 2005.

 

-> Adding a full text search catalog to the database – To add a full text search catalog we need to open the database node in Management studio and click on the storage node. We can see the Full text catalog in this node. We have an option for adding the full-text catalog by which we can add a new catalog.

 

-> Adding the Full text indexing table – We need to specify to the database that we want to implement the full-text search functionality for a table. To do this we can select the respective table and select the right click option of Full-Text Index and Define Full-Text index.

 

This opens up the Full text indexing wizard.

 

1. Using the Full text indexing wizard we select a unique index.

 

2. Once we select a unique index and move to the next step, we select the columns that are eligible for full text queries. This enables the full-text search for the selected columns.

 

3. We need to select the option of how can we track the changes. If automatically selected then a full population of indexing occurs. To avoid a population at the end of this wizard, select the Do Not Track Changes option, and clear the Start Full Population When Index Is Created checkbox.

 

4. Once we are done with step 3 we need to map the catalog out of all the catalogs at the database level. Or we could even create a new catalog for the full text search.

 

5. This is the last step of the wizard. Here we can create a table schedule and catalog schedules and schedule them with time. A table schedule can have a name, time of schedule and occurrences.

 

Once we create them and click on the finish button the full-text index is created.

 

 

Running the Full Text search :

Until now, we have set-up the full text search catalogs and indexes. Let us run the some queries for the full-text search. There are four types of predicates used in SQL Server 2005 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.

 

Listing 2 :

FREETEXT ( { column_name | (column_list) | * }

, ‘freetext_string’ [ , LANGUAGE language_term ] )

 

Listing 3 :

SELECT CellularPhone FROM CART WHERE FREETEXT (CellularPhone, ‘1111’)

 

Listing 4 :

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.

 

Listing 5 :

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.

 

 

 

Listing 6 :

SELECT [Name], [Address]

FROM Customer

WHERE AGE > 30

AND CONTAINS([Name], ‘Uday’);

 

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 2005 database.