Creating Full Text Catalog and Full Text Search...

Creating Full Text Catalog and Full Text Search

           Full text indexing is a great feature used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CONTAINS() with “and” or “or” operators. 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. These feature works with RTM (Ready to Manufacture) version of SQL Server 2008



 

Create FULL TEXT INDEX

Select a respective table on which you need to apply the FULL TEXT INDEX

 

Change the Full Text Catalog Drop down data to the respective value as shown below

Populate the Index

 

Once the Index Is created and populated, you can write the query and use in searching records on that table which provides better performance

 

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

 

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

 

Select EmployeeID, Title From HumanResources.Employeer Where Freetext (*,Marketing Assistant')

 

 

Contains with AND Condition.

Select EmployeeID, Title From HumanResources.Employeer Where Contains(*,Marketing and Assistant')

 

 

Contains with OR Condition.

Select EmployeeID, Title From HumanResources.Employeer Where Contains(*,Marketing Or Assistant')

 

 

 

Regards

Daniel J

Dataware and Data Mining Architect...