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.