SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

September 5, 2008 by pinaldave

Full Text Index helps to perform complex queries against character data. These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. Software developer Monica who helped with screenshots also informed that this feature works with RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions.

To create an Index, follow the steps:

Create a Full-Text Catalog
Create a Full-Text Index
Populate the Index

1) Create a Full-Text Catalog

Full – Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

3) Populate the Index

As the Index Is created and populated, you can write the query and use in searching records on that table which provides better performance.

For Example,

We will find the Employee Records who has “Marking “in their Job Title.

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

Separates the string into individual words based on word boundaries (word-breaking).
Generates inflectional forms of the words (stemming).
Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

USE AdventureWorks2008GO

SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee WHERE FREETEXT(*, 'Marketing Assistant');

SELECT BusinessEntityID,JobTitle FROM HumanResources.Employee WHERE CONTAINS(JobTitle, 'Marketing OR Assistant');

SELECT BusinessEntityID,JobTitle FROM HumanResources.Employee WHERE CONTAINS(JobTitle, 'Marketing AND Assistant'); GO

Conclusion

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | Tagged Full Text Search | 27 Comments

27 Responses

on September 5, 2008 at 11:03 pm | Reply Jerry Hung

We use SQL 2008 Full-Text here

It works good, most of the time

However it is really a black box that’s hard to customize, especially if you want to weight different columns, different terms, etc…
on September 12, 2008 at 2:59 am | Reply amoako atta amofa

am beginner i want to start from beginner
on September 12, 2008 at 3:08 am | Reply amoako atta amofa

please help me
on September 16, 2008 at 8:06 pm | Reply Erdal Findik

Hi,

I installed recently SQL2008 on a new server. I moved my databases from the old server(SQL 2005) to the new one.
Everything is woeking fine…except Full text search.

FTS is very slow on SQL 2008, I deleted and recreated the FTS from scratch but still very slow.

Can somebody has a similar issue?

Thx.
on September 19, 2008 at 9:21 am | Reply Oyen

Can you please share the link where I can download the AdventureWorks2008 .mdf file?

Codeplex is a mess.
on September 24, 2008 at 8:43 pm | Reply Sarita

Great Post!

I have started creating a full text index and I have a question –

If I create a Full Text Search Index on multipal tables should I be creating a new catelog for each tables?
on October 6, 2008 at 6:22 am | Reply jmscotty

Thank you for the walk though. Very informative as usual. A very minor detail but one that might confuse some readers is that you wrote:

“We will find the Employee Records
who has “Marking“ in their Job Title.”

I believe the word “Marking” should have been “Marketing”.
on October 8, 2008 at 5:36 pm | Reply Iman

Useful ,Thank you.
on December 20, 2008 at 5:21 pm | Reply Mehran Zand K.

I have started creating full text search index on sql server 2008 and everything is works fine .
on December 23, 2008 at 7:38 am | Reply sree

hi,
This post is very nice. I developed fulltext indexing on one table in sql server 2005. Initially it worked well but now its dead slow ,please any one suggest me what to do, client is not satisfied with this one.

Thanks
on December 24, 2008 at 12:49 am | Reply sree

hi pinal,
any suggestions…
on December 31, 2008 at 3:55 am | Reply peyman

Thank u so much , very useful …
on January 16, 2009 at 9:02 pm | Reply Wesly

Could you also post the sql code that does the same thing as in Microsoft SQL Server Management studio
on January 29, 2009 at 3:28 pm | Reply ZUBAIR ALAM

thanks it was really helpfull
on March 12, 2009 at 6:32 am | Reply John

I tried this example with the Adventurer dbase and I get the following error.

Msg 30046, Level 16, State 1, Line 2
SQL Server encountered error 0×80070218 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

I have checked that service (SQL Full-text Filter Daemon Launcher (MSSQLSERVER)) is running and I have run the sp (sp_fulltext_service ‘restart_all_fdhosts’) and that also gives me the same error.

Any Ideas?

THnx John
on March 16, 2009 at 2:23 am | Reply Jim Geurts

@John, I had the same problem. Try going to Sql Server Configuration Manager and change the full-text daemon launcher to use the same account as the SQL Server instance. That fixed it for me.

Start -> Programs -> Microsoft Sql Server 2008 -> Configuration Tools -> Sql Server Configuration Manager
on March 18, 2009 at 10:29 pm | Reply Lee

Looking for more examples of Full Text Search but its either Microsoft’s few samples or these samples on this page.
on May 18, 2009 at 12:32 pm | Reply yash

hi.. Sir

how can i use column name in freetext string field

exp :

select cat_name from tbcat_res c,tbanswer a where freetext(c.cat_key,a.ans_cat)
on July 22, 2009 at 5:29 am | Reply mon

Hi,

I installed and migrted my data from sql2000 to sql2008.

Everything was migrated successfully except that I’m having an error when trying to fulltext index a image field.

Can somebody help me on this one..

thanks..
mon
on July 29, 2009 at 11:23 am | Reply Vijay Jadhav

Hi all,

I’m getting below error when I run query (I have created full text catalog)

Msg 30046, Level 16, State 1, Line 1
SQL Server encountered error 0×80070422 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

What would be the reason for this ? Please help me to resolve this problem.

Thanks.
on July 29, 2009 at 4:52 pm | Reply Vijay Jadhav

Hi all,

Got it.

Please check it at : http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/8b33c58e-9d6f-462a-9a6a-1784946bb073?prof=required

Thanks.
on August 3, 2009 at 6:11 pm | Reply Mike

Thanks Jim Geurts – you just solved a problem for me that was causing no end of heartache. Cheers!
on August 19, 2009 at 11:35 pm | Reply rosebabu

Hi Pinaldev,

i’ve a full text search catalog on my DB(ex: abcd) and while populating it ran successfully but the population status was still idle.

The abcd DB is restored from another SQL server machine. both sql server configurations are same as mentioned below.

while checking the SQL server log i’ve found the below error message on the FT catalog population process.

“SQL Server failed to communicate with filter daemon launch service (Windows error: The service cannot be started either because it is disabled or because it has no enabled devices associated with it.). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.
Error: 9954 Severity: 16 State: 1.”

i’m using SQL Server 2008 (Standard Edition, version: 10.0.1600.22)

To run the Full-Text search catalog successfully, what i’ve to do. please give me any suggesstions to run this FT search.

Thanks, Rosebabu.
on August 20, 2009 at 7:10 pm | Reply rosebabu

Hi,

The error stated on my comment is fixed by starting “SQL Full-text Filter Daemon Launcher” service. and i made this service to start automatically.
Now i’m not having the error as ‘SQL Server failed to communicate with filter daemon launch service…”.

while trying to populate the FT catalog now, i’d error on the “Full-Text Search crawl log”. the log file was located in %ProgramFiles%\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG folder

and in that folder the log file has the following error message.

“A fatal error occurred during a full-text population and caused the population to be cancelled.
Population type is: FULL;
database name is adj001 (id: 9);
catalog name is adjcat001 (id: 11);
table name tblAssociation (id: 2117582582).
Fix the errors that are logged in the full-text crawl log. Then, resume the population.
The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION.”

Please give any suggesstion to run the FT searching successfully on the db server.

Thanks,
Rose.
on August 20, 2009 at 11:31 pm | Reply rosebabu

Hi,

Never Mind for further discussion,

Finally i got the solution for my problem (Full Text Index Searching).

while installing the SQL server 2008 i selected all the services to be installed and the service a/c as Network Service.

Now i went to Administrative Tools->Services then i selected the “SQL Full-text Filter Daemon Launcher” service.
then right click it and on the “Log on” tab, i changed the log on as “Local System Account” and restarted the service.
and as well as i did for the “SQL Server Agent” and restarted the service.

Now the Full text searching is populating and running fine.

Thnks.
on September 1, 2009 at 5:57 pm | Reply Dinesh

Original Article was very useful to me!

Thanks Pinal.
on September 25, 2009 at 4:08 pm | Reply Hiren Solanki

Nice artical like your baby shaya

keep rocking.. bhai