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

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:

  1. Create a Full-Text Catalog
  2. Create a Full-Text Index
  3. Populate the Index

1) Create a Full-Text Catalog

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 1_Catalog

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 2_CatalogName
SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 3_CatCreated

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

2) Create a Full-Text Index

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 4_FullIndex

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 5_FullIndex

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 6_FullIndex

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 7_index

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 8_FullIndex

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 9_FullIndex

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 10_index

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 11_FullIndex

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 12_success

3) Populate the Index

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 13_populate

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 14_populateSuccess

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

SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search 15_Query

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)

, , ,
Previous Post
SQLAuthority News – Download SQL Server Related Products
Next Post
SQL SERVER – Find Primary Key Using SQL Server Management Studio

Related Posts

154 Comments. Leave new

  • 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

    Reply
  • Good Article to u/stand the full text index…thanks

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Carlos Silvestre Vaca Justiniano
    March 7, 2013 2:09 am

    Gracias por tu ayuda eres una person muy buena al realizar estas explicaciones.

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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

    Reply
  • 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’)

    Reply
  • 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.

    Reply
  • 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 ?

    Reply
  • 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

    Reply
  • Chirag Patelchirag
    May 7, 2014 6:47 pm

    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

    Reply
  • 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….

    Reply
  • can search unicode character using fulltext index??

    Reply
  • rahul saldanha
    April 28, 2015 10:46 am

    how to use full text index with SQL join statement

    Reply
    • USE AdventureWorks2012;
      GO
      SELECT Description
      FROM Production.ProductDescription
      WHERE ProductDescriptionID 5 AND
      CONTAINS(Description, ‘Aluminum AND spindle’);
      GO

      Reply
  • 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

    Reply
    • Yuval – you can use
      1. Start > Run > Services.msc
      2. Use SQL Server Configuration Manager.

      Reply
  • 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

    Reply
  • 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.

    Reply
  • 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)”‘)

    Reply

Leave a Reply

Menu