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
Good Article to u/stand the full text index…thanks
Greetings Team,
Please anyone help me on this ….
I have some data ,
In procedure going to Retrieve data using like operator its working fine but my requirement is exact match will come first followed by other liking data…
please let me know any way to display records ..asap plz
I have very large data ,
i want search both title and description field ,indexing already implimented on table but while we search on abstract field then it takes more time to retrive data pls help me how can i relevence search.
Gracias por tu ayuda eres una person muy buena al realizar estas explicaciones.
Hi Pinal,
I have a SQL 2008 database that was moved off a corrupted system. Problem is they didn’t take the Full-Text catalog. Now I can’t seem to find any way to reattach the database and it’s log file to a new SQL 2008 instance. It’s throwing errors about the catalog missing. Is there a way to fake it out, or to simply attach using a script that informs SQL to NOT attempt to utilze the FTCAT that the database is referencing?
-Rob
I need something like this
SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, ‘Mark’);
And return all rows that contains Mark, such as
markert
marketing
mark
but does not work.
What can i do?
Hi All,
Say that my table gets refreshed every week with millions of records, in this case shouldn’t Full Text Index be refreshed every week as well or it takes care of it automatically?
– Siva Prasad
I am currently using IN clause on a varchar field. Will using Contains of FTS help in performance? For e.g.
Select * from Orders where City IN (‘London’ , ‘New York’)
v/s
Select * from Orders where Contains (City, ‘London or New York’)
Hi,
I am using freetexttable for bringing search results but when i am using ‘a+’ word, it is not bringing the results with exact word ‘a+’, it is bringing results with ‘a’ only. please help me.
Hi Pinal, Thanks for the article
Is this feature similar to tsvector and tsquery datataypes in postgresql ?
I am doing a project where i need search on ICD10CM database. Can this feature be useful in doing that ?
Hi Pinal,
I have been writing a store procedure for a searching few joined tables,(eg customer table,product table,category table) I have used like ‘% @searched word % for major column including all 3 tables.
Q1: Which one would have good performance like or Full Textsearch ?
Q2:Can I have full text search query by joining 5 to 6 table linked with each other through pk,fk
Thanks,
Ashraf
Hi Pinal,
Can you please share your Idea For Full Text Search v/s Solr Search. which is better for very large database( around 75 GB+)
Today Also I had Try to search with Solr API(http://lucene.apache.org/solr/). it Give Result of 28K Searchable Result in Just 4 ms(MYSQL Database).So this criteria is also work with SQL Full Text Search.and i Get same result in same or less then this duration.
Because I want to transfer my Database mySQL to SQL Server. and my Application from PHP To .Net.
So please tell me better to use ? Solr or SQL Full Text Catalog.
Thanks,
Chirag
Hi,
how to increase the query performance of full text search if we created full text search into 3 tables.
Select *
FROM table1 T1 Inner join Table2 T2
ON T1.Id = T2.Id Inner join table3 T3
ON T1.Id = T3.Id
Where FreeText(T1.ProductName,@FulltextSearch)
OR FreeText(T2.Keywords,@FulltextSearch)
OR FreeText(T3.Summary,@FulltextSearch)
Please help on it….
can search unicode character using fulltext index??
how to use full text index with SQL join statement
USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID 5 AND
CONTAINS(Description, ‘Aluminum AND spindle’);
GO
Hi,
How do I verify if FTS service is running?
I didn’t find it in any of the following:
sp_fulltext_service, FULLTEXTSERVICEPROPERTY, SERVERPROPERTY
Thanks,
Yuval
Yuval – you can use
1. Start > Run > Services.msc
2. Use SQL Server Configuration Manager.
Hi,
I created a SP answering if FTS service is running:
CREATE PROCEDURE dbo.uspIsFTServiceOn
AS
DECLARE @R BIT = 0;
BEGIN TRY
EXECUTE(N’DECLARE @A BIT; SELECT @A = 1 FROM dbo.TextSearchServiceTest WHERE CONTAINS(*, ””WordDoesNotExists””)’);
SET @R = 1;
END TRY
BEGIN CATCH
RETURN @R;
END CATCH;
RETURN @R;
Where “TextSearchServiceTest” is empty TFS indexed table.
Enjoy,
Yuval
Great. thanks for sharing.
I was able to get the full text search working on IMAGE column. Try if this works for you. I had to convert the IMAGE column to VARCHAR(MAX) and reloaded the data by following this conversion – CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), )). Then I created the FULL TEXT INDEX mechanism and repopulated my indexes. I did not install any filters for PDF as well. So far so good, CONTAINS and FREETEXT queries return results.
Thanks Sathya.
why Contains Function in Sql server query never fetch exact result if search keyword contain Brackets. following is my query that is not returning correct result but Like operator with contains returned correct result
Select * from TableName
where Contains(FieldName, ‘”257(1)(e)”‘)
Thanks its very helpful to me