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
Really Awesome Post….. :)
PS, this is just a sample resume that I loaded, the text looks like this. As you can see the word “great” is in there …
Resume for: Joe Bloggs
I’m a great guy with all the skills you’re after:
• Aaaaaaaaa
• Bbbbbbbbbbbbbb
• Cccccc
• Ddddddddddddddddddddddddddddddddd
• Eeeeeeeeeeeeeeeeeeee
Thanks for the chat.
If there any way where to configure the default path for the Full Text Catalogs?
Hi Pinal Dave,
Full text search is not available on SQL Azure. Is there an alternative search technique that we can use on SQL Azure.
Hi Pinal,
I have created a full text index to search pdf content using a keyword. The problem is that if after adding a pdf and searching it immediately, it does not give the result using full text search, but I can see the row added in the table. If i delete the full text index and create and refresh the database, then the pdf appears on searching.
I followed the same steps provided above to create full text in sql server 2005
Thanks
Hi
If word is too large (more than 65 characters) then FullText search is not working in SQL Server 2008.
Table Employee is having value “MostViewModelclassesneedthesamefeaturesTheyoftenneedtoimplementtheINotifyPropertyChanged” for column FullTextSearchContent in one of the row.
Following query works properly.
SELECT * FROM
Employee
WHERE Contains(FullTextSearchContent,
‘ “MostViewModelclassesneedthesamefeaturesTheyoftenneedtoimplementtheINotifyPropertyChanged” ‘)
Following query will not return any row as ‘*’ is being used. Here if i keep my word to 64 characters then this will also work.
SELECT * FROM
Employee
WHERE Contains(FullTextSearchContent,
‘ “MostViewModelclassesneedthesamefeaturesTheyoftenneedtoimplementtheINotifyPropertyChanged*” ‘)
Please let me know why this happens.
Table: Customer
Full Text Index on: strLOB, strMGL, strName
—————————————————————————————————–
strLOB strMGL strName
—————————————————————————————————–
COMPLETE PURCHASING Fred Babbie Fred Victor Centre
COMPLETE PURCHASING Fred Babbie Cardinal Flahiff Basilian Centre
COMPLETE PURCHASING VACANT Basilian Center
COMPLETE PURCHASING Fred Babbie Fred Douglas Lodge
COMPLETE PURCHASING Fred Babbie Fred Douglas Heritage House
——————————————————————————————————
Need to fetch rows having words ‘Fred’ AND ‘Basilian’ both. I tried the below query but no result found.
SELECT strLOB, strMGL, strName
FROM Customer
where contains(*, ‘Fred AND Basilian’)
Please let me know how this can be achieve.
Table: Customer
Full Text Index on: strLOB, strMGL, strName
———————————————————————————————————-
strLOB strMGL strName
———————————————————————————————————-
COMPLETE PURCHASING; Fred Babbie; Fred Victor Centre
COMPLETE PURCHASING; Fred Babbie; Cardinal Flahiff Basilian Centre
COMPLETE PURCHASING; VACANT; Basilian Center
COMPLETE PURCHASING; Fred Babbie; Fred Douglas Lodge
COMPLETE PURCHASING; Fred Babbie; Fred Douglas Heritage House
———————————————————————————————————
Need to fetch rows having words ‘Fred’ AND ‘Basilian’ both. I tried the below query but no result found.
SELECT strLOB, strMGL, strName
FROM Customer
where contains(*, ‘Fred AND Basilian’)
Please let me know how this can be achieve.
Very nice post! thank you!
Cheers dude
Hi,
Above article is very nice, but i need full text search with varbinary(max)
Hi,
full text search working for varchar columns but i need full text search for varbinary(max) column and column not accepting while creating full text index columns
A Note on Full Text, Performance, and Versions:
As always, it is a joy to read Pinal’s posts. Thank you! I would like to add my research in this area. I have been looking into solving the search query challenge in my database and have found some helpful additions to the data above.
(1) Why does Full Text Indexing offer better performance than using LIKE keywords or other non-full text Indexed approaches?
Answer: LIKE ‘%searchWord%’ will need to perform a scan of all items in a given column; indexing is not used. If you want to optimize on performance using indexes for search patterns you need to enable Full Text Indexing as shown above.
(2) Which “keywords” are better?
Answer: This depends on what you need to do. As Pinal stated, CONTAINS has less overhead than FREETEXT. In most cases FREETEXT is overkill. Further, there are a lot of features to CONTAINS to consider. See the MSDN site for more details, or URL below. Note: FREETEXT and CONTAINS do not offer “priority” features directly. You can use the FREETEXTTABLE or CONTAINSTABLE predicates for this capability (aka. the other two full text predicates avaialble)
I found this “sitepoint” article very insightfull:
(3) Differences in Deployment of Full Text Indexing:
– SQL 2005 and earlier you need to depend on the external service (MS Search Services … I think they were called)
– MSSQL 2008 + finally put this feature into the product (no external service, and backups include indexed files / catalogues)
– MSSQL 2012 has made some changes to word breaking and other features. You will need to rebuild indexes if you are moving to this version.
Hope this is found usefull by all you fine coders out there! Happy indexing, and special thanks to Pinal for his amazing educational guides!
Thanks!
Muchas gracias, muy útil como siempre
How are full text catalogs and indexes implemented in 2008 and higher? I see some MS articles that claim a folder is created for the catalog and other places says the implementation is virtual but having problem finding out what that means. Thank you for you blog and your great site.
Awesome article. Will publish this page link in my personal blog also.
Many thanks.
Dasun
When I’m searching for ean references like 5413736209503 in a full text index catalog I’m getting far more results than exptected (5000+) instead of only one specific article. What’s blocking the search for numbers only references?
Any ideas?!
is this possible with vs2008
Dear Pinal,
I have created full-text search on the table and consider two columns only.
Example
Table Name : Tank
Tank Name | Fuel Name
[1] V01 | JP8
[2] V02 | DSLW
[3] BAG01 | JETA1
[4] BAG04 | AVGAS
if i use below state and not return the rows.
select tankname,fuelname from tank where contains(*,'”v01″ and “jp8″‘)
AND operator check both value in two column and not return the row.
OR condition is working.
Please let me know how this can be achieve.
Thanks,
Utpal
hi utpal,
Did you check the @(*,’”v01″ and “jp8″‘)@ with changing the inverted commas ?