Full-text search in SQL Server

In this SQL Server tutorial, we will learn about full-text search in SQL Server. Here we will learn How to enable and implement a full-text search in SQL Server. And for the implementation, we will be using SQL Server 2019.

Here is the list of topics that we are going to discuss.

• What is full-text search in SQL Server
• Architecture of full-text search in SQL Server
• How to check if the full-text search is installed in SQL Server
• How to install full-text search in SQL Server
• How to install full-text search in SQL Server Express
• How to enable full-text search in SQL Server
• How to setup full-text search in SQL Server using wizard
• Implementing full-text search in SQL Server

In SQL Server, for faster query execution, we generally use clustered or non-clustered indexes. But these indexes have their own set of limitations and can’t be used for quick text lookup.

For example, a LIKE operator forces SQL Server to analyze the entire table for the values that match the given expression. Even if we create an index for a given column, the execution will not always be fast.

Now, to overcome this issue, SQL Server came up with a new Full-Text Search feature. So, let’s first understand what is full-text search.

What is full-text search in SQL Server

A full-text search is a feature available in SQL Server and Azure SQL databases. And it allows users and applications to undergo a full-text query against character-based data in a SQL Server table. This feature helps to perform character-based lookups efficiently by an index referred to as Full-Text Index.

Now, we can define this full-text index on top of one or more columns for a particular table. And the columns in the table can be of the following data types.

• char 
• varchar 
• nchar
• nvarchar
• text
• next
• image
• XML
• varbinary(max)
• FILESTREAM

The Full-Text indexes are created and used in the context of a given language, such as English or French. And the full-text queries perform the search of words or phrases based upon the rules of that particular language.

• Now, we can use words, phrases, or their various variants in a full-text query.
• And the query will return any document that carries at least one match (also known as a hit).
• And a match generally occurs when a document holds all the given terms in the full-text query and also satisfies the search criteria.

So, with this, we got an idea about what is full-text search and why we need to use it in SQL Server.

Read: Try catch in SQL Server stored procedure

Architecture of full-text search in SQL Server

Till now, we got an idea about what is a full-text search in SQL Server. Now, let’s understand its architecture and working in more detail.

The Full-Text search architecture mainly consists of 2 processes.

1. The SQL Server Process
2. The filter daemon host process

Each process consists of many components, and each component has an important role in the full-text search architecture.

To understand the architecture, we have to understand each component and its relationship with other components.

So, let’s understand each process and its components one by one.

SQL Server Process

The SQL Server process (sqlservr.exe) consists of the following components.

• User Tables: These are the SQL Server tables that contain the data to be full text indexed.
• Full-text gatherer: This component is responsible for driving and scheduling index population for monitoring.
• Thesaurus files: These files comprise synonyms for the terms that are been searched.
• Stoplist objects: This component consists of a list of common words that can be ignored. Because these words are not relevant for a lookup.
• SQL Server query processor: It is a query processor which compiles and executes SQL queries. And if the given query consists of a full-text search query then, the query is sent to Full-Text Engine.
• Full-Text Engine: This component is integrated with the SQL Server query processor. And it is responsible for compiling and executing full-text queries.
• Index Writer (Indexer): This component helps in creating the structure which is used to store indexed tokens.
• Filter daemon manager: This component monitors the status of the Full-Text Engine filter daemon host.

Filter Daemon Host Process

This filter daemon host process (fdhost.exe) is started by the Full-Text Engine. And this process handles multiple full-text search components used for accessing, filtering, word breaking tasks. Its list of components is given below.

• Protocol handler: The protocol handler is responsible for pulling data from the memory for further processing. And it is also responsible for accessing data from the user table.
• Filters: We cannot directly full-text index every data type in SQL Server, some data types required some sort of filtering. For example data in varbinary, image, or XML. So, depending upon the document type, we can use filters to remove the embedded formatting on the text.
• Word breakers and stemmers: Word breakers are used to discover word boundaries based upon the lexical rules of that language. And each word breaker is used in pair with stemmer. A stemmer is used to discover the root of the verb also based upon language-specific rules.

Read: Stored procedure for search functionality in SQL Server

How to check if the full-text search is installed in SQL Server

The Full-Text Search is an optional component of the SQL Server Database Engine. And we usually get an option to install this feature while installing the SQL Server. So, if we didn’t select this feature at the time of installation then, we need to run the installation setup again.

And if we have already installed the full-text search feature then, this feature is enabled by default. Let’s understand how to check if the full-texed feature is installed or not in SQL Server.

For this implementation, we simply need to execute the following SQL query.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') 
AS [FULLTEXTSERVICE]

Now, if the above query returns “1” then, it means that the full-text search feature is installed and enabled. And if the query returns “0” then, it means that the feature is not installed. Here is an example related to the output of this query.

In our case, the query return “0” so, it means the full-text search feature is not installed. So, next, we will see how to install the full-text search feature in SQL Server.

Read: How to select latest record in SQL Server

How to install full-text search in SQL Server

So, in this section, we will understand how to run the SQL Server installer to install the Full-Text Search feature. The steps for its installation are given below.

• First, locate your SQL Server installer from the “C:\” drive and run the setup as administrator. It will open a new installation center window.

• Now, in the installation center, open the installation page and click on the “New SQL Server stand-alone installation or add features to an existing installation” option.

• After this, we will get the following SQL Server update window. And now, we need to click on “Next“.

• Next, it will install the setup files as prerequisites and then, we need to click on the “Next” button.
• Once we reach the “Installation Type” page, we need to click on the “Add feature to an existing instance of SQL Server” radio button and click on “Next” to proceed. It will open a Feature Selection Page.

Note: We cannot use these steps directly to install the Full-Text Search feature in the Express Edition. For Express Edition, first, we need to download the Express Advanced package.

• In the Feature Selection Page, tick mark the “Full-Text and Semantic Extraction for Search” option. And then click on “Next“.

• Next, click on “Next” option for all other pages to install the feature. In the end, we will get the following page after successfully installing the feature.

Read: SQL Server stored procedure vs function

How to install full-text search in SQL Server Express

In the case of SQL Server Express Edition, we have to download the Express Edition with advanced services. Here are some of the additional steps that we need to follow in the case of SQL Server Express Edition.

• First, we need to download the installer for SQL Server Express Edition. To download it, open this link and click on “Download” option. It will start downloading the installer.
• After downloading the installer, run the installer and first, it will ask for installation. In this case, we have to select the “Download Media” option.

• In the next page, we have to select the package type that we want to download. So, we have to select the “Express Advanced” package and click on “Download“.

• Once we click on download, it will start download of the Express Advanced package at the specified location.

• Now, once the download is completed, open the location where we have downloaded the package. And run the downloaded file. It will extract the Express Advanced package to the specified directory.
• Now, we simply need to open that directory and run the installation media. And then, we can follow the steps given in the previous section to install the Full-Text Search feature.

Read: SQL Server create stored procedure

How to enable full-text search in SQL Server

In SQL Server, all the SQL Server databases are full-text enabled by default. But before implementing a full-text search on a table, we have to create a full-text catalog as well as the full-text index on the tables.

So, to enable a full-text search on a table, we should know how to create a full-text catalog and a full-text index on the table that we want to search. In this section, we will understand both the steps before start implementing a full-text search.

How to create Full-Text Catalog

A catalog in SQL Server is a virtual object that does not belong to any filegroup. And it is a logical concept that refers to the collection of full-text indexes. So, each full-text index must belong to a full-text catalog.

We can have a separate catalog for each full-text index and we can also have multiple indexes in one catalog.

Let’s understand how to create a full-text catalog using Transact-SQL in SQL Server. Here is the syntax for creating a full-text catalog.

USE database
GO
CREATE FULLTEXT CATALOG catalog_name

In the above syntax, first, we have to specify the database in which we want to create a catalog. Next, we have used the “CREATE FULLTEXT CATALOG” statement to create a full-text catalog with a name that we will specify in place of catalog_name.

Let’s use the above syntax to create a new full-text catalog in SQL Server. And the code for the example is given below.

USE [sqlserverguides]
GO

CREATE FULLTEXT CATALOG FullTextCatalog

In the above example, we are creating a full-text catalog with the name “FullTextCatalog” in the “sqlserverguides” database. Now, if the above command is executed successfully then it simply means that the catalog is been created.

How to create Full-Text Index

Before creating a full-text index, we have to assure that the table has a unique single-column, non-nullable index. And for this task, we have to follow the following syntax given below.

USE database
GO
CREATE UNIQUE INDEX index_name ON schema.table(column1);  

By using the above syntax, we can create an unclustered unique index on the specified column of the table. Let’s understand the syntax by executing a simple example. And the example is given below.

USE [sqlserverguides]
GO
CREATE UNIQUE INDEX ui_Stud ON dbo.Student(id);  

In the above example, we are using the CREATE UNIQUE INDEX statement to create a ui_Stud index on the id column in the Student table. Here is the screenshot of the above execution.

Now, let’s understand how to create a full-text index in SQL Server. First, let’s see the syntax of creating a full-text index.

CREATE FULLTEXT INDEX ON table_name  
   [ ( { column_name   
             [ TYPE COLUMN type_column_name ]  
             [ LANGUAGE language_term ]   
             [ STATISTICAL_SEMANTICS ]  
        } [ ,...n]   
      ) ]  
    KEY INDEX index_name   
    [ ON <catalog_filegroup_option> ]  
    [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]  

The above syntax accepts the following arguments.

• table_name: It is used to define the name of the table or view which consists of the column that needs to be indexed.
• column_name: It is used to define the column to be indexed.
• type_column_name: This argument is used to define the type column name. we only need to specify this argument when the column is of varbinary(max) or image data type.
• language_term: Here we need to specify the language in which the column data is stored.
• index_name: It is the name of the unique index in the table.
• catalog_filegroup_option: It is used to specify the catalog name.

Now, let’s use this syntax to create a full-text index in the sqlserverguides database. And here is the SQL query for the example.

CREATE FULLTEXT INDEX ON dbo.Student 
(  
    University_Name                  
        Language 1033        --1033 is the LCID for English - United States  
)  
KEY INDEX ui_Stud ON FullTextCatalog
WITH CHANGE_TRACKING AUTO          
GO  

In the above example, we have created a full-text index on the “University_Name” column which is in the Student table. For the language, we have defined the LCID for American English. Here is the screenshot of the execution of the above example.

Read: SQL Server trigger after insert

How to setup full-text search in SQL Server using wizard

In the previous section, we have discussed how to enable and set up the full-text search using Transact-SQL. But, there is one more way to set up the full-text search in SQL Server. And for this method, we have to use the SQL Server Management Studio.

In SQL Server Management Studio, we can use the Full-Text Indexing Wizard to set up the full-text search in SQL Server. The steps for this execution are given below.

• In the Object Explorer, right-click the table for which you want to create the full-text index. Next, we need to select the “Full-Text Index” option and click on “Define Full-Text Index“.

• The above given action will launch the “Full-Text Indexing Wizard” in a new window. We simply need to click on the “Next” button.

• Next, we need to select the unique index from the list of indexes and then, click on the “Next” button.

• After this, we have to select the columns for which you want to enable the full-text queries. And after selection, click on the “Next” button.

• On the next page, we have to select the tracking option for the index. In our case, we have selected the “Automatic” tracking option.

• After this, we have to select the catalog for the full-text index. Now, if we already have a catalog then, we can select from the given list. Else, we can also create a new one by selecting the “Create a new catalog option“.

• After this, the wizard will display the summary for the full-text indexing where we can review all the options that we have selected. After the review, we have to click on “Finish” button to create the full-text index.

• Now, once the full-text index is created successfully, we will get the following success page.

Read: SQL Server stored procedure if else

Implementing full-text search in SQL Server

Till this point, we have discussed what is a full-text search, how to install it, and how to set up this feature in SQL Server. Now, in this section, we will understand how to implement a full-text search on a full-text indexed column in SQL Server.

In SQL Server, there are 4 options that we can use while creating full-text search queries. And these four options are CONTAINS, FREETEXT, CONTAINSTABLE, and FREETEXTTABLE.

Now, we can use the CONTAINS and CONTAINSTABLE in the queries to match words and phrases. Whereas, we can use the FREETEXT and FREETEXTTABLE to match the meaning but not the exact word or phrase.

Now to understand the implementation of a full-text search query in SQL Server, we are going to use the CONTAINS predicate. And for this implementation, we will continue with our Student table from the sqlserverguides database. The data in the Student table is as follows.

So, while creating a full-text index in our previous example, we have used the “Universuty_Name” column. And for the full-text search, we will use the same column to find the columns which consist of the given text. Here is a simple full-text search query.

USE [sqlserverguides]
GO

SELECT * FROM dbo.Student
WHERE CONTAINS(University_Name,'University')

In the also query, we are using the SELECT statement to select all the columns from the Student table. And we are also using the CONTAINS predicate with the WHERE clause to find the rows which consist of the “University” word in the “University_Name” column.

So, after the execution of the above query, we will get the following result.

Now, let’s execute another example with the help of the FREETEXT keyword. And for this example, we will try to search a phrase from the “University_Name” column. The query for this example is given below.

USE [sqlserverguides]
GO

SELECT * FROM dbo.Student
WHERE FREETEXT(University_Name,'New York')

By using the above query, we are trying to search all the university names which carry “New York” in them. And the output of this query is shown in the image below.

You may also like to read the following SQL Server tutorials.

• PostgreSQL vs SQL Server
• SQL Server User Permissions
• Rename stored procedure in SQL Server
• SQL Server move database files
• SQL Server stored procedure output parameter
• SQL Server select from stored procedure
• How to view stored procedure in SQL Server
• Remote procedure call failed in SQL Server
• Error: 40 – could not open a connection to sql server
• SQL Server view order by

In this tutorial, we learned about full-text search in SQL Server. Here we have discussed How to enable and implement a full-text search in SQL Server. And here is the list of topics that we have covered.

• What is full-text search in SQL Server
• Architecture of full-text search in SQL Server
• How to check if the full-text search is installed in SQL Server
• How to install full-text search in SQL Server
• How to install full-text search in SQL Server Express
• How to enable full-text search in SQL Server
• How to setup full-text search in SQL Server using wizard
• Implementing full-text search in SQL Server