Full Text Indexing is a powerful feature in SQL Server that allows for complex queries against character data. With Full Text Indexing, you can perform word or phrase searches on your data, making it easier to find the information you need.

To create a Full Text Index, you need to follow these steps:

1. Create a Full-Text Catalog
2. Create a Full-Text Index
3. Populate the Index

Create a Full-Text Catalog

A Full-Text Catalog is a logical container for Full Text Indexes. You can create a Full-Text Catalog while creating a Full-Text Index using the wizard. This step is essential to organize and manage your Full Text Indexes effectively.

Create a Full-Text Index

Once you have created the Full-Text Catalog, you can proceed to create the Full-Text Index. Remember, only one Full-Text Index is allowed per table or indexed view. You can include up to 1024 columns in the index.

Populate the Index

After creating the Full-Text Index, you need to populate it with data. This step is crucial as it allows you to perform efficient searches on the indexed table. Once the index is created and populated, you can write queries to search for specific records based on your search criteria.

For example, let’s say we want to find all employee records with “Marketing” in their job title. We can use the FREETEXT() predicate to search for columns containing character-based data types. This will not match the exact word, but rather the meaning of the words in the search condition.

Here’s an example query:

USE AdventureWorks2008
GO
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

In addition to FREETEXT(), you can also use the CONTAINS() predicate to search for specific keywords in the records. The difference is that CONTAINS() requires you to provide the exact keyword to match, and if you want to combine multiple words in the search, you need to use “and” or “or” operators.

Here are a few examples:

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing OR Assistant');

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing AND Assistant');

Conclusion

Full Text Indexing is a valuable feature in SQL Server that solves the problem of searching textual data columns for specific words and phrases. By using Full Text Indexing, you can search for words, phrases, and multiple forms of a word or phrase using FREETEXT() and CONTAINS() with “and” or “or” operators.

By understanding and utilizing Full Text Indexing, you can improve the performance and efficiency of your SQL Server database queries.