Implementing Full-Text Search in SQL Server 2016 for beginners

This article talks about the full-text search and its implementation for the beginners to get started.

Additionally, the readers will know the important things to make the full-text search work properly.

This article also highlights the importance of using Full-Text Search to match text patterns and meet similar sorts of requirements.

About Full-Text Search

Let’s understand the Full-Text Search with the help of definitions.

Simple Definition

Full-Text Search is used to optimally search character-based data for quick answers.

Microsoft Definition

Full-Text Search in SQL Server and Azure SQL Database (cloud version of SQL database) let users and applications run full-text queries against character-based data in SQL Server tables.

What is Full-Text Query

A Full-Text query is a special kind of query which is written and run against columns with textual data to find patterns of data. For this matter, Full-Text Search should be enabled for those columns.

Compatibility

Full-Text Search is compatible with the following SQL Server versions:

1. SQL Server 2005 and upward
   
2. Azure SQL Database

Full-Text Search Modern Version

In the recent SQL Server versions such as SQL 2016, Full-Text Search is available for installation alongside Semantic Search.

Full-Text Search – SQL Server Optional

Please remember that Full-Text Search is not installed by default when you install SQL Server. You have to install it optionally by adding more features to your current SQL instance using the setup you originally used to install your SQL Server.

Full-Text Search – Database Default

All the SQL databases are ready to be used with Full-Text Search by default. No further installation is needed except the requirements before using Full-Text Search in a SQL database.

Case Sensitivity

According to the Microsoft documentation, Full-Text Search is not case sensitive so the words “control panel”, ”Control panel”, and “control panel” are all treated the same.

Setup Full-Text Search

As mentioned, you need to add Full-Text Search as a feature to your existing SQL Server installation using the same setup file you used to install SQL Server.

Run SQL Installer

Start by running the SQL Server installer. It gives you an option to mount as a drive if you prefer to run directly from the installer rather than saving it.

Run Setup File

Click the Setup.exe file to run the SQL Server setup:

Add as a Feature

As soon as you run the setup file, some initial checks will be carried out. Once these checks are passed, you need to select “add features to an existing installation option” under the Installation navigation bar (section):

Choose your Current Server

Next, choose your current/potential server for which you are going to install Full-Text Search. In our case, it is SQL 2016:

Select Instance Feature to be added

Next, select the Full-Text and Semantic Extractions for Search feature to be added (if you add this feature for older SQL versions, Semantic Extractions may not be shown):

Please note, since I have already added this feature before the walkthrough, it appears greyed out on the screenshot.  But for anyone who is adding it for the first time, it will be active and can be installed which takes a little while.

Check Full-Text Search Installation Status

Once Full-Text Search is installed, you can check it by running the following T-SQL script against the master database:

-- Is Full-Text Search installed then 1 or 0

SELECT fulltextserviceproperty('IsFulltextInstalled') as [Full-Text Search]

Once you have successfully installed Full-Text Search, you will see “1” meaning the script is running:

Searching Words and Phrases using Full-Text Search

Now, we will perform some basic search operations for words and phrases using Full-Text Search.

Setup Sample Database

To understand the basic use of Full-Text Search, let’s set up a sample database called SQLDevBlogV6 as follows:

-- Create sample database (SQLDevBlogV6)

CREATE DATABASE SQLDevBlogV6;

GO





USE SQLDevBlogV6;



-- (1) Create Article table in the sample database

CREATE TABLE [dbo].[Article] (

[ArticleId] INT IDENTITY (1, 1) NOT NULL,

[Category] VARCHAR (50) NULL,

[Author] VARCHAR (50) NULL,

[Title] VARCHAR (150) NULL,

[Published] DATETIME2 (7) NULL,

[Notes] VARCHAR (400) NULL,

CONSTRAINT [PK_Article] PRIMARY KEY (ArticleId)

);



GO



-- (2) Populate the table with data

SET IDENTITY_INSERT [dbo].[Article] ON

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (1, N'Development', N'Atif', N'Introduction to T-SQL Programming ', N'2017-01-01 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (2, N'Testing', N'Peter', N'Database Unit Testing Fundamentals', N'2017-01-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (3, N'DLM', N'Sadaf', N'Database Lifecycle Management for beginners', N'2017-01-20 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (4, N'Development', N'Peter', N'Common Table Expressions (CTE)', N'2017-02-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (5, N'Testing', N'Sadaf', N'Manual Testing vs. Automated Testing', N'2017-03-20 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (6, N'Testing', N'Atif', N'Beyond Database Unit Testing', N'2017-11-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (7, N'Testing', N'Sadaf', N'Cross Database Unit Testing', N'2017-12-20 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (8, N'Development', N'Peter', N'SQLCMD - A Handy Utility for Developers', N'2018-01-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (9, N'Testing', N'Sadaf', N'Scripting and Testing Database for beginners ', N'2018-02-15 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (10, N'Development', N'Atif', N'Advanced Database Development Methods', N'2018-07-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (11, N'Testing', N'Sadaf', N'How to Write Unit Tests for your Database', N'2018-11-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (12, N'Development', N'Peter', N'Database Development using Modern Tools', N'2018-12-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (13, N'DLM', N'Atif', N'Designing, Developing and Deploying Database', N'2019-01-01 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (14, N'DLM', N'Peter', N'How to Apply Database Lifecycle Management', N'2019-02-10 00:00:00', NULL)

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (15, N'Testing', N'Saqib', N'SQL Unit Testing Stored Procedures', N'2019-03-10 00:00:00', NULL)

SET IDENTITY_INSERT [dbo].[Article] OFF

Phrases and Words

Let’s view more phrases and words in the context of Full-Text Search. This will give you have a better idea of what you are looking for via Full-Text Search.

The statement “Introduction to T-SQL” is a phrase and “introduction” and “T-SQL” are the words of interest.

Steps to Implement Full-Text Search in SQL Server

A Full-Text Search is implemented in the following ways:

1. Create a Full-Text Catalog (to store Full-Text indexes).
   
2. Define Full-Text Index on Table or Indexed View.
   
3. Run Full-Text Search Queries using CONTAINS or FREETEXT to find words and phrases.

Create a Full-Text Catalog

So, the sample database (SQLDevBlogV6) has been created and populated successfully. Creating a Full-Text Catalog is the first step to implement Full-Text Search.

Go to Object Explorer in SQL Server, expand Databases node, and then Click on SQLDevBlogV6.

Click Storage, then Click Full-Text Catalogs, and then Click New Full-Text Catalog:

Enter the catalog name as DevBlogCatalog, Click OK:

The newly created Full-Text Catalog looks as follows:

Define a Full-Text Index on Table

Right Click on the Articles table, Click Full-Text index, and then Click Define Full-Text index as shown below:

This will trigger the Full-Text Indexing Wizard. Click Next, then click Next again after ensuring that the table primary key is preselected by the wizard.

In the next step, select the Title column for Full-text queries. This is the column against which we are going to run our Full-Text queries:

Next, select the default option to automate change tracking by selecting the Automatically option (unless you are interested to select some other option) as shown below:

In the next step, please choose the Full-Text Catalog (DevBlogCatalog) to associate with the Full-Text Index you defined earlier in this walkthrough. Then Click Next after choosing the default options as shown below:

Click Next and skip the optional step, then click Finish to see that the Full-Text Index has been created successfully.

We can now run Full-Text queries against the Title column of the table Article thanks to enabling Full-Text Search.

Search for word Testing using Full-Text Query

We can now quickly search for words by using the CONTAINS keyword (predicate) by writing the following T-SQL script:

-- Search for the Word Testing using Full-Text Query

SELECT * FROM dbo.Article

WHERE CONTAINS(Title,'Testing')

The results to search the Testing word in the Title column are as follows:

Please note that you can get the same results using Like operator without Full-Text Search. The difference is when you are running this query against millions and millions of rows and this is when LIKE operator struggles. Meanwhile, CONTAINS is far quicker according to the experts.

Search for Phrase for beginners using Full-Text Query

Let’s find all the articles where the phrase “for beginners” is used in the title. This will help beginners get started quickly.

This time we are using the FREETEXT keyword (Predicate). It will get us all the articles for beginners by using the following T-SQL script:

-- Search for Phrase: for beginners using Full-Text Query

SELECT * FROM dbo.Article

WHERE FREETEXT(Title,'for beginners')

The output is as follows:

Congratulations, you have successfully learned the basics of Full-Text Search. You also got hands-on experience of setting up and running Full-Text search queries for words and phrases.

Stay in touch as the upcoming article describes the advanced Full-Text Search usage. It is often useful in database analysis scenarios.

Things to do

Now that you can setup Full-Text Search and run Full-Text queries, please try the following things to improve your skills:

1. Try populating the database Notes column by providing more information about the articles. It should be followed by defining the Full-Text Catalog and running Full-Text queries to search for words and phrases using the CONTAINS and FREETEXT keywords.
   
2. Also, search for the Word Unit to find out all the articles where this word is mentioned. It may be stored in the column as Unit Testing, Unit Test, or Unit Tests.
   
3. Refer to the sample database in this article. Setup Full-Text Search for the table Product, define the Full-Text index on the column name, and add as many records as you can. Search for words and phrases of your choice to find their desired products (names).