Reader Level:
ARTICLE

Creating Full Text Index in SQL Server

Posted by Krishna Garad Articles | SQL January 03, 2012
In this article we will see how to create a full text search index for a SQL Server database for effective searching.
  • 1
  • 0
  • 24830

Introduction

In this article we will see how to create a full text search index for SQL Server database for effective search. In so many cases we need to provide the search facility in our application. To provide searching facility within our local database we can use the greater feature provided by Microsoft SQL Server i.e. full text index.

Background

When we want to use the full text index service we need to start the service first else it will raise the errors. So let's start with how to start the service and use the Full text index step by step.

Step 1

Open SQL Server and create a new database with the following table for eg. Tbl_Search but keep in mind that the full text index only works on primary key or unique key containing tables.

Create Table Tbl_Search
(
Id Int Primary Key Identity(1,1),
Title Varchar(500),
[Desc]Varchar(max)
)

Insert some rows in the created table.

Step 2

Now we will create full text index on our database and table for providing search in table. So create the Full Text Catalog on our database by using following query.

CREATE FULLTEXT CATALOG FTSearch

In the query above we have created FullText Catalog with the name FTSearch.

Step 3

Now we will create full text index on our table Tbl_Search but for that we require the unique key id or primary key id so find the id of unique or primary key by using following command.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This command will display the all constraint names on tables present in our database from the output. Copy the Tbl_Search constraint name for creating a full text index on TblSearch.

CREATE FULLTEXT INDEX ON Tbl_Search

(Title, [Desc] LANGUAGE 1033)

KEY INDEX PK__Tbl_Sear__3214EC0700551192

ON FTSearch

In the above statement you can see we are creating a FullText Index on table name with a parameter; this parameter is nothing but the column name of the table on which we want to create the full text index and language 1033 denotes the language English.

Step 4

Now it's time to search the records in a specified indexed table. For that we can write the queries like below.

Select * from Tbl_Search Where Contains(Title,'Asp.Net')

Select * from Tbl_Search Where Freetext([Desc],'Asp.Net')

In the preceding queries you can see we have given a where clause with column name which is the column we want to search and what we want to search. The preceding queries retrive the rows of a table which contain ASP.Net in title column and the second query will retrieve the rows of ASP.Net in the desc column.

In some cases your queries gives an error like fdhost cannot be started. That means your FullTextIndex Service is not started; for that you have to first start the Demon Launcher for FullTextIndex service.

Step 5

For starting FullTextIndex service go to SQL Server Tool->SQL Server Configuration Manage->Service->FullTextSearch Demon Launcher if it is stopped then start this service for performing search operation with a contains and FreeText clause and restart the SQL Server instance.

Conclusion

In this way we can use the FullTextIndex on our database.
 

Article Extensions
Contents added by Prabu Ram on Jan 05, 2012

This article has a nice explanation on how to use SQL server feature for searching. However, I feel that this article is not covered one thing : what if the Full Text Search feature is not installed in the SQL server?

I want to say what to do in this case so that it helps someone who may encounter this problem. Here are two ways of testing whether this feature installed or not. This feature is available for all editions of SQL Server 2005 and 2008 (including SQL Express)

1. Check the services applet (run -> type 'services.msc') for this entry :
    SQL Server FullText Search Or SQL Server FullText Search (SQLEXPRESS) (in case of SQL Express). Start this service of not running

2. Run the query "select fulltextserviceproperty('isfulltextinstalled')" in the Query analyzer. if the result is '1' then it is installed else not.

In the case of unavailability of this feature, you need to install by downloading the "SQLEXPR_ADV.EXE" from http://www.microsoft.com/express/sql/download/default.aspx

But the problem here is that this "exe" file cannot be run directly,Instead, you have to use Programs and Features (in Windows Vista) or Add/Remove Programs (in Windows XP), find the Microsoft SQL Server entry, and select Change. Then opt to update the database engine (not the workstation components), and select the Full-Text Search feature from the Database Services node.

The installer doesn’t have access to the necessary setup files, so it will eventually ask for them. You get these files by unzipping (not executing) sqlexpr_adv.exe using a program such as WinZip or WinRAR, and extracting its contents to a folder on your disk. This folder contains a setup folder that you can then supply to the SQL Server setup program.
After the setup finishes, restart the SQL Server service and execute the query shown earlier to make sure you have correctly installed the Full-Text Search feature.

 

Contents added by James Robin on Jan 04, 2012
CREATE FULLTEXT CATALOG FTSearch

Executing this command in SqlServer Express gives the error:

Cannot use full-text search in user instance.

I suspect this is because Express does not support Full Text indexing.

If it does, then what is the remedy?






COMMENT USING

Trending up