Skip to main content

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 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. Software developer Monica who helped with screenshots also informed that 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 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 word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.

Comments

Post a Comment

Popular posts from this blog

ASP.NET MVC - Set custom IIdentity or IPrincipal

Here's how I do it. I decided to use IPrincipal instead of IIdentity because it means I don't have to implement both IIdentity and IPrincipal. Create the interface interface ICustomPrincipal : IPrincipal { int UserId { get ; set ; } string FirstName { get ; set ; } string LastName { get ; set ; } } CustomPrincipal public class CustomPrincipal : ICustomPrincipal { public IIdentity Identity { get ; private set ; } public bool IsInRole ( string role ) { return false ; } public CustomPrincipal ( string email ) { this . Identity = new GenericIdentity ( email ); } public int UserId { get ; set ; } public string FirstName { get ; set ; } public string LastName { get ; set ; } } CustomPrincipalSerializeModel - for serializing custom information into userdata field in FormsAuthenticationTicket object. public class CustomPrincipalSerializeMode...
8 comments
Read more

Validate credit card number with Mod 10 algorithm in C#

Introduction All you know what information contains in your NIC number. But do you know what information contains in the Credit Card Number? Following article provides brief details about what information contain in your credit card and demonstrates to how to validate credit card number using mod 10 (Luhn) algorithms with C#. Background  Card Length   Typically, credit card numbers are all numeric and the length of the credit card number is between 12 digits to 19 digits.  14, 15, 16 digits – Diners Club 15 digits – American Express 13, 16 digits – Visa 16 digits - MasterCard   For more information please refer  http://en.wikipedia.org/wiki/Bank_card_number . Hidden information  Major Industry Identifier (MII)   The first digit of the credit card number is the Major Industry Identifier (MII). It designates the category of the entry which issued the card.     1 and 2 – Airlin...
1 comment
Read more

kendo ui menu dynamic - ASP.NET MVC

Model Class public class DynamicMenu {     public int MenuID { get; set; }     public string MenuName { get; set; }     public int ParentMenuID { get; set; } } Controller Code public ActionResult Menu() {      ViewData["DynamicMenu"] = new List<DynamicMenu> {             new DynamicMenu{ MenuID = 1, MenuName = "Self", ParentMenuID = 0 },             new DynamicMenu{ MenuID = 2, MenuName = "Functional", ParentMenuID = 0  },             new DynamicMenu{ MenuID = 3, MenuName = "Leave", ParentMenuID = 1 },             new DynamicMenu{ MenuID = 4, MenuName = "Leave Application", ParentMenuID = 3 },             new Dynamic...
1 comment
Read more