Click here to Skip to main content
Click here to Skip to main content

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

, 10 Sep 2008
Rate this:
Please Sign up or sign in to vote.
First article on topic - SQL SERVER - 2008 - Creating Full Text Catalog and Full Text Search

Introduction

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. 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:

  1. Create a Full-Text Catalog
  2. Create a Full-Text Index
  3. 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 it in searching records on that table which provides better performance.

For example, we will find the Employee Records that have "Marking" in their Job Title.

FREETEXT( ) is the 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 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 the 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 word or phrase using FREETEXT() and CONTAINS() with "and" or "or" operators.

Reference

History

  • 10th September, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
QuestionFull Text search with noise words PinmemberMember 1087719716-Jul-14 0:21 
Questionsearch with Contains() for single digit is not working Pinmembersravan_34116-Dec-13 21:41 
GeneralMultiple Language - Language Word Breakers PinmemberEdwinY5-May-10 18:58 
QuestionNothing regarding CONTAINSTABLE and Ranking? PinmemberAndrei Rinea15-Sep-08 14:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 10 Sep 2008
Article Copyright 2008 by pinaldave
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid