Full Text Index helps to perform complex queries against character data. These queries can include words 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 Monica, who helped with screenshots also informed that this feature works with the RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions. Let us learn about Creating Full Text Catalog and Full Text Search in this blog post.
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 AdventureWorks2008 GO 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 a word or phrase using FREETEXT () and CANTAINS () with “and” or “or” operators.
Reference : Pinal Dave (https://blog.sqlauthority.com)
154 Comments. Leave new
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…
This is good one
am beginner i want to start from beginner
please help me
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.
Whenever you run FTS for the first time. It will take very long time to load your data. But once it’s completed means you get the required results. Then you will not have any performance issues.
I hope this would have helped @Erdal Findik, what a timing, but bht dair kar di mehrban aatay aatay ;)
Can you please share the link where I can download the AdventureWorks2008 .mdf file?
Codeplex is a mess.
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?
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”.
Useful ,Thank you.
I have started creating full text search index on sql server 2008 and everything is works fine .
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
hi pinal,
any suggestions…
Thank u so much , very useful …
Could you also post the sql code that does the same thing as in Microsoft SQL Server Management studio
thanks it was really helpfull
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 0x80070218 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
@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
Looking for more examples of Full Text Search but its either Microsoft’s few samples or these samples on this page.
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)
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
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 0x80070422 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.