Full Text Indexing

How to make Full Text Indexing Easier in a Database of large number of tables

Full-text search refers to the functionality in SQL Server that supports queries against character-based data. These types of queries can include words and phrases as well as multiple forms of a word or phrase. A full-text index is made up of collection of words that are derived from the text being indexed. So, for implementing a full text index, we have to create a catalog first and then this catalog should be mapped with tables and corresponding columns to be indexed in the database.

The 3 sections to create full text indexing. they are,

·         Create a Full Text Catalog

·         Create Full Text Index for the columns in different Tables

·         Populate the Index

Step 1: Select the database. Under Storage select ‘Full Text Catalogs’. Create a New Full Text Catalog  

            

Step 2: Give a name for the catalog and click OK.

You can see the catalog under Full Text Catalogs in storage.

Step 3: You can create the index in 2 ways. I am explaining both here.

Method 1

  Right Click on the catalog created and select properties.

 

Select the Tables/View from the left pane.

         (A)   Select the table name to which you want to create and put it in the right box. Here we have selected the table ‘tbl_company’ as table to be indexed.

          (B)   Select the unique index

          (C)   Select the column to be indexed from available columns. Here we have selected the ‘company_name’ column to be indexed.

Then click OK.

Method 2

Right Click the Table to be indexed. Select Full Text Index and the again select ‘Define Full Text Index’

It will display a Full Text Indexing Wizard. Click on Next Button. Select the unique index.

Select the columns to which you want indexing

Select the catalog created in the first step. If you want to create a separate catalog, you can create the same by checking ‘Create a New Catalog’.

Step 4: Right click on the table. Select Full Text and again select Start Population

Contains & FreeText

Contains:  It can search for a word or phrase

        (1)     Exact Match for a word or phrase. Use double quotes

                 Select * from table_name

                 where contains(search_content,'"John"')

     

     (2)    Searching for the word ‘Application’

Select * from tbl_search_index where contains(search_content,'Application')

       

    (3)    FreeText

Select * from tbl_search_index where freetext(search_content,'Application')

Advantages

·         Faster Search

·         Character Based Search

·         Boolean searches

·         More relevant results

Conclusion

Full Text Search is a powerful and useful feature. It is easy to implement and gives users the ability to quickly search through huge amount of data.