Creating FullText Index in SQL Server


In this article I'm trying to explain how to create FullText Index in SQL servers, using this we can able to search multiple matched text while searching data. We all are know that we can able to search single text using like keyword but we can't fetch multiple text search using like keyword for that we go to freetext search in SQL servers.

Creating FullText Index in SQL Server :



In this article I'm trying to explain how to create FullText Index in SQL servers, using this we can able to search multiple matched text while searching data. We all are know that we can able to search single text using like keyword but we can't fetch multiple text search using like keyword for that we go to freetext search in SQL servers.

Description:



Freetext search in SQL server is one of the great features to give multiple search resulted data. But without creating FULLTEXT if you try to use FreeText then it will throw error.

"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Search' because it is not full-text indexed."

To overcome the above issue we must create FULLTEXT catalog, after that using that table constraint name we can able to create FULLTEXT INDEX. Once FULLTEXT INDEX has been created for that particular table then you can able to search multiple texts.

You can achieve this by follow the below steps.

Step-1:

Create a table like below in your database.


create table Search
(
id int primary key identity(1,1),
name varchar(200)
)


Step-2:

Create FULLTEXT catalog in your database. Use below query for creating FULLTEXT CATALOG.


CREATE FULLTEXT CATALOG FULLTEXTSearch


Step-3:

After create catalog in your database, then find the constraint name of that particular table using below query.


SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME ='search'


Step-4:

Now this is the time to create FULLTEXT INDEX, for this we must and should have a key index. Using that key index of the table we can able to create FULLTEXT INDEX for that particular table.


CREATE FULLTEXT INDEX ON Search

([name] LANGUAGE 1033)

KEY INDEX PK__Search__3213E83FED0050D2

ON Search


Note: keep in mind we can able to create FULLTEXT INDEX for only Primary Key and Unique key containing tables only.

Step-5:

Now you can able to search multiple search strings using "freetext" search in sql servers.
Refer below sample for that.


insert into Search ( name)
values('john'),('james'),('sam'),('karthi')

select * from Search where freetext(name,'James John Sam')



Conclusion:



This article will help you to search multiple strings while searching.


Article by naveensanagasetti
I hope you enjoyed to read my article, If you have any queries out of this then please post your comments.

Follow naveensanagasetti or read 139 articles authored by naveensanagasetti

Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: