---
title: "Full Text Search Index"  
description: "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."  
author: "Niraj Kumar Mishra"  
published: 2017-08-11  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/12528/full-text-search-index  
category: "mssql server"  
tags: ["database"]  
reading_time: 3 minutes  

---

# Full Text Search Index

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](https://www.mindstick.com/forum/159667/how-to-migrate-the-sql-server-database-to-a-lower-version).\

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](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-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](https://www.mindstick.com/mindstickarticle/4e1ff3d3-5b07-4799-b443-6c4f6f911dc0/images/3d81ab3a-8aa3-419f-9ada-8b1bb25ceeb7.png)\

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](https://www.mindstick.com/mindstickarticle/4e1ff3d3-5b07-4799-b443-6c4f6f911dc0/images/cb449e54-d66a-4d8a-a40b-27a780c63a29.png)\

\

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](https://www.mindstick.com/mindstickarticle/4e1ff3d3-5b07-4799-b443-6c4f6f911dc0/images/08e83c02-9ae2-4105-afc5-d7df561bd50f.png)\

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](https://www.mindstick.com/mindstickarticle/4e1ff3d3-5b07-4799-b443-6c4f6f911dc0/images/800cc63e-691d-483d-b470-987ff7d6cb28.png)\

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](https://www.mindstick.com/blog/12797/iowa-is-expected-to-see-heavy-growth-in-multiple-sectors) 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](https://www.mindstick.com/forum/157686/explain-the-various-functions-available-for-manipulating-the-character-data-in-database).

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](https://www.mindstick.com/interview/23094/what-is-the-common-numeric-data-types-in-mysql) type column. These column data types are: char, varchar, char, nvarchar, text, ntext, image, xml, or varbinary.

A full-text [query returns](https://www.mindstick.com/forum/159429/sql-query-returns-a-timeout-expired-error) 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](https://www.mindstick.com/articles/13159/operating-press-brakes-an-ultimate-guide-for-brake-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](https://www.mindstick.com/mindstickarticle/4e1ff3d3-5b07-4799-b443-6c4f6f911dc0/images/800cc63e-691d-483d-b470-987ff7d6cb28.png)\

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

\

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

\

![Full Text Search Index](https://www.mindstick.com/mindstickarticle/4e1ff3d3-5b07-4799-b443-6c4f6f911dc0/images/1ad3886a-232e-49b5-b5c0-e2d3b0f4f603.png)

\

I Hope you are Understand these Example Carefully. Thank you

\

\

---

Original Source: https://www.mindstick.com/articles/12528/full-text-search-index

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.