In this article we will show Why we use Full-text Search Index and how to create a full-text search index for a SQL Server database.

First of All I want to show why we use Full-text Search, So When any User are want to search any record into the Database table then he is use “Where“ keyword and some cases he use ‘Like’ keyword.

But problem behind this , if you are want to search any record by using Where clause then it show only Exact matches record from table Example We have a Table  “Hotels”

Full Text Search Index

And I want to search all record from table that have Hotels words in Topic field. So my query is.

select *from Hotel where Topic='Hotels'

 

and it show..


Full Text Search Index


This result means, it not found any record. And then  I check Like keyword instead of it


select *from Hotel where Topic LIKE '%Hotels%'

 


and then it produce the output as this..


Full Text Search Index

It show all records that have Hotels word.

 

 

Show it is clear that "where" keyword is used for the full text of the column and the


"like" keyword is used for parts of the column.

 

Again I use Like keyword:

select *from Hotel where Topic LIKE '%Hotels Above%'



Full Text Search Index

It show only one record , Because in other words the "like" keyword is used for the full text or part of the column. It does not support:

  1.     Two words near each other
  2.     Multiple words with distinct weightings. 


So that reason we use Full-text Search View

Full-text Search View

Full Text Index helps to perform complex queries with against character data. 

When  you  use  FTS  queries  it  can  include  words  or  phrase  searching.  Before  executing  of  full text  queries in  Sql  server , firstly  we  need  to  create  a  full-text  index  on  the  table.  Only  one  Full  text  search  Index  are allowed  on  one  table  that  contain maximum1024  columns.

full-text index  includes  one  or  more  character-based columns in the table it not allow Numeric data type column. These  column data types are: char, varchar, char, nvarchar, text, ntext, image, xml, or varbinary.

A full-text query returns any document that contain at least one match .

How I can Create Full-text Index
 Firstally you create Full Text Catolog and then create Index as follow
 CREATE FULLTEXT CATALOG FTSearch
 CREATE FULLTEXT INDEX ON Hotels
   (Topic, [Asc] LANGUAGE 1033)
    KEY INDEX PK__ Hotels __3214EC0700551192
    ON FTSearch
 

      

 Full Text Index can be used to search words, phrases and multiple forms of a word or phrase using


 FREETEXT  (), CONTAINS () with “and” or “or” operators (FREETEXT, CONTAINS).  


Again if I want to same query that are pass above example with LIKE  keyword



    select *from Hotel where Topic LIKE '%Hotels Above%'

Full Text Search Index

   same query if I pass with FREETEXT() then what its result


 
   select *from Hotel where freetext(Topic,'Hotel Above')


 Full Text Search Index


   I Hope you are Understand these Example Carefully. Thank you

 


 


  Modified On Nov-29-2017 03:30:16 PM

Leave Comment