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
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.
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.
sql server full text index multiple columns, sql server full text index performance, sql server full text index status
Source: http://docphy.com/technology/computers/software/sql-server-2008-creating-full-text-catalog-full-text-search.html
No comments:
Post a Comment