Tuesday, August 2, 2016

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 MonicaMonica, 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

2 comments:

  1. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training

    ReplyDelete