Implementing Full Text Search in SQL Server

Discussion in 'SQL Server' started by MinalS, Dec 26, 2014.

  1. In SQL Server, the LIKE operator is used for searching the text value in a particular column. The synonyms and antonyms of the specific word are searched using the full text search. The full text search feature is the default and enabled in SQL Server. In this article, we shall learn about the full text search feature in SQL Server.

    Configuring the Full Text Search



    The full text search feature allows user to search for the text in the SQL server tables. The columns having data types as char, nchar, varchar, text, ntext, xml, image, varbinary, and FILESTREAM can be used.

    If the amount of data is large, it takes longer time to retrieve the data from the corresponding table. The full text search helps user to retrieve the data by improving the searching speed.

    To retrieve the data using the full text search, the full text search is configured on the database. The following steps are to be performed while creating the full text search.

    1) Create a full text catalog

    The full text catalog provides user with an interface for storing the full text indexes. Once the full text search is enabled, the full text catalog can be created. A full text catalog contains multiple full text indexes.

    The following statement is used for creating the full text catalog in SQL server.

    Code:
    
    CREATE FULLTEXT CATALOG Cat1 AS DEFAULT
    
    
    2) Create a Unique Index

    Once the full text catalog is created, user needs to check for the unique index on a table. The index can be mapped with the values available in the index. The existing index can be used on the new one.

    The following code is used for creating the unique index on the table.

    Code:
    
    CREATE UNIQUE INDEX In_Prod ON Product ( ProductID )
    
    
    3) Creating the Full Text Index

    The full text index can be created on the table when the full text catalog and unique index are created on the table. The full text index is used for storing the information about words and their position in the column. User can use this information for the full text queries for searching the words or combination in a table. They are created on the base tables and not on the view or system tables.

    A concept of stopwords is added in the searching mechanism. The stopwords are used for restricting the query and are excluded from the string. An object of the database is used for managing these stopwords. The object is known as stoplist. The stoplist can be used with the full text index.

    The full text index can be created using the CREATE FULLTEXT INDEX statement. The syntax for creating the FULLTEXT INDEX is as shown below:

    Code:
    
    	CREATE FULLTEXT INDEX ON Table_name
    	( Column_name )
    	KEY INDEX index_name 
    	[ ON fulltext_catalog_name ]
    	[ WITH [ CHANGE_TRACKING = { MANUAL | AUTO | OFF [, NO POPULATION]
    	[ STOPLIST = { OFF | SYSTEM | stoplist_name } ] ]
    
    
    Where,

    Table_name is the name of the table for which the FULLTEXT index is created.

    Column_name is the name of the column in the full text index.

    KEY INDEX index_name is the name of the unique key index on the table.

    fulltext_catalog_name is the name of the full text catalog for the full text index.

    CHANGE_TRACKING is used for checking the changes made to the table columns in the full text index.

    The list of values that can be added are as shown below:
    1. MANUAL: It states that the changes must be propogated
    2. AUTO: It states that the changes must be automatically propogated
    3. OFF [ , NO POPULATION ] : It specifies that the SQL Server is not keeping the track of the changes.
    STOPLIST is used along with the full text stoplist and an index. The following values are available in it.
    1. OFF: It is used to specify that no stoplist is associated with the full text index.
    2. SYSTEM: It is used as a default full text system stoplist used with the full text index.
    3. stoplist_name: It is the user defined stoplist along with the full text index.
    Consider the following example to create full text index on the ProductName column.

    Code:
    
    CREATE FULLTEXT INDEX ON Product ( ProductName ) KEY INDEX Ix_ProdName
    
    
    The above statement creates the full text index on the Product table. The index is based on the Ix_ProdName is created.

    Populating the Full Text Index



    Once the full text index is created, user needs to populate the data with the columns enabled for the full text feature. The process of population is used for populating the full text index. The process of population fills the pages with the word and their position on the page.

    The CHANGE TRACKING option is used to keep the track of the changes made to the indexed data. The option is off by default. If the user does not want the full text index to be populated, the option is kept to OFF.

    The following code demonstrates the full text index on the Product Table.

    Code:
    
    CREATE FULLTEXT INDEX ON Product ( ProductID ) KEY INDEX PK_ProductID WITH CHANGE_TRACKING OFF, NO POPULATION
    
    
    To populate the index, the following statement is executed.

    Code:
    
    ALTER FULLTEXT INDEX ON Product START FULL POPULATION
    
    
    The steps for creating a full text index in SQL Server are as mentioned below:
    1. In the Object explorer window, expand the Storage node from the list.
    2. Select Full Text Catalog option from the list.

      [​IMG]
    3. Select the New Full-Text Catalog option from the list. The new window for adding the catalog name will open.
    4. Enter the name as EmpFullTextCatalog and select owner as DBO. Select the Sensitive option. Click OK.

      [​IMG]
    5. Click on the Full Text Index node, select Full Text Index option and click Define Full Text Index.

      [​IMG]
    6. Full Text Indexing Wizard will open. Click Next.
    7. Add the Unique Index value in the drop down list and click Next.

      [​IMG]
    8. Select the table columns and the language for the columns as English. Click Next.

      [​IMG]
    9. Select the automatically option from the list and uncheck the Start population checkbox.

      [​IMG]
    10. Select the full text catalog as EmpFullTextCatalog, index file group as PRIMARY and full text stoplist as <system>. Click Next

      [​IMG]
    11. In the Define Population Schedules window, click Next.

      [​IMG]
    12. In the Full Text Indexing Wizard Description, the complete description is mentioned. Click Finish to complete the process.

      [​IMG]
    13. The Full Text Indexing Wizard Process window shows the success of the creation of the full text index. Click Close.

      [​IMG]
    14. Next, Start populating the full text index. Select the table from the list click Full-Text index option, select Start Full Population.

      [​IMG]
    15. The success of the Full Index Population is displayed.

      [​IMG]

    Searching data through the full text search



    Once the full text index is created, the query can be created using the full text predicates. The predicated specify the way words should be searched in the search column. The following predicates can be used for the searching the full text search.

    1) FREETEXT

    The predicate is used for searching the columns that contain the character based data types. The variation for the specified words is searched in the column. The FREETEXT is used for searching the prefixes.

    Consider the following example where the employeedata table is considered.

    Code:
    
    select emprole, empname from employeedata where freetext ( *, ‘Manager’)
    
    
    The following output is displayed to the user.

    [​IMG]

    2) FREETEXTTABLE

    It is used for searching the character type columns and returns the table of rows containing the words with similar meaning. It contains the plurals, gender and verb forms.

    The SELECT statement is used along with the FROM clause as used in the table data. The output is the table containing the column named as KEY. It contains the full text key values. It has a unique value that matches the selection criteria.

    It also contains a RANK column. The values in the column can range from 0 to 1000. The highest ranking can be returned using the ORDER BY clause.

    The syntax for creating the FREETEXTTABELE is as shown below:

    Code:
    
    FREETEXTTABLE ( table, { column_name | ( column_list ) | * }, ‘search_string’ )
    
    
    Where,

    table describes the name of the table.

    column_name is the name of the full text indexed column of the table

    column_list is the list that is used to specify the columns.

    search_string is the text to be searched

    Consider the following example to demonstrate the FREETEXTTABLE predicate.

    Code:
    
    select * from employeedata as f inner join ( select [ KEY ], RANK from FREETEXTTABLE ( employeedata, * , ‘Man’, 2) as d ON d.[Key] = f.empid ORDER BY d.rank DESC
    
    
    The output for the code is as shown below:

    [​IMG]

    3) CONTAINS

    It is the predicate used in queries for searching a specific phrase or the exact match.

    The following code demonstrates the CONTAINS predicate.

    Code:
    
    select empid, empname, emprole from employeedata where contains ( [emprole], ‘Manager OR Lead’) 
    
    
    The output for the code is as shown below:

    [​IMG]

    4) CONTAINSTABLE

    The search result is returned as the tabular output. It consists of two columns as KEY and RANK column. The rows present in the table are joined to the source table. The SELECT statement along with the FROM clause is used.

    Consider the following example to demonstrate the use of the CONTAINSTABLE predicate.

    Code:
    
    select * from employeedata join ( select [key], RANK from CONTAINSTABLE ( employeedata, *, ‘Lead’ )) as d ON d.[key] = employeedata.empid
    
    
    The output for the code is as shown below:

    [​IMG]
     
    Last edited by a moderator: Jan 21, 2017
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice