This article tells about advanced methods of Full-Text Search usage for data professionals. It enables better search experience in their day-to-day database analysis.

Additionally, the readers will get beyond basic knowledge of Full-Text Search and its implementation. You will see the importance of advanced searching methodology using Full-Text Search to get quick and reliable results.

Prerequisites

First, let’s go through prerequisites since the article is focused on advanced uses of Full-Text Search.

T-SQL Know-How

The article assumes that to implement the walk-through(s), the readers are familiar with writing and running T-SQL scripts against databases with a basic understanding of SQL Server.

Basics of Full-Text Search

This article also expects the readers to have a basic understanding of Full-Text Search.

Please, refer to the Implementing Full-Text Search in SQL Server 2016 for Beginners article if you’d like to get familiar with basic concepts and implementation of Full-Text Search via walk-through(s).

Full-Text Search Installation

Let’s assume that Full-Text Search has already been added to your SQL instance.

If you haven’t done it yet, follow the instructions in the Implementing Full-Text Search in SQL Server 2016 for Beginners article to help you install Full-Text Search to your SQL instance.

You can check the status of Full-Text Search by executing the following script:

-- Is Full-Text Search installed then 1 or 0
SELECT fulltextserviceproperty('IsFulltextInstalled') as [Full-Text Search]

Sample Database SQLDevBlogV7

Now create and populate a sample database called SQLDevBlogV7 having Notes column of VARCHAR(MAX) type. To do it, run the following T-SQL script against the master database:

-- Create sample database (SQLDevBlogV7)
CREATE DATABASE SQLDevBlogV7;
GO


USE SQLDevBlogV7;


-- (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 (MAX) NULL,
CONSTRAINT [PK_Article] PRIMARY KEY (ArticleId)

);

GO

-- Reset tabular data
TRUNCATE TABLE dbo.Article
GO

-- Add data to the table
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', N'The article is about T-SQL programming covering most commonly used statements including SELECT.')

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', N'This is a database unit testing article to get familiar with unit testing with the help of examples and walkthroughs.')

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (3, N'Data Analysis', N'Haroon', N'Learn Basic Data Analysis with SQL Window Functions', N'2019-06-12 00:00:00', N'This article is about T-SQL (Transact-SQL) Window functions and their basic use in day-to-day data analysis tasks.')

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', N'This is an article which explains how to use CTE in T-SQL to write complex queries.')

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', N'This article is based on the comparative anlaysis between manual testing and automated testing. This article uses tSQLt to give examples of automated testing.')

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', N'There is a lot to do when it comes to the database unit testing and this article is more about the some advanced level methods to write and run SQL unit tests using tSQLt.')

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', N'This article covers a complex scenario of cross database unit testing using 3rd party testing tools.')

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (8, N'Development', N'Haroon', N'How to Properly Use the T-SQL IsNumeric Function', N'2018-01-10 00:00:00', N'This is about IsNumeric function which determines whether an expression can be evaluated as a number. Here, the expression can consist of symbols and operators which are evaluated as a single data value by SQL Server Database Engine.')

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', N'This article is based on the scripting and testing of SQL databases using modern tools and technologies.')

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', N'This article talks about modern database development tools including SSDT, SSMS etc.')

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', N'This article is about writing unit testing for your database using MSTest.')

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', N'In this aritlce a database development scenario is dicussed using SQL Server Data Tools and SQL Server Management Studio. ')

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', N'The concepts around Azure DevOps and their implementation is the main focus of this article.')

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', N'This article is about DLM.')

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', N'This article talks about how to write unit tests for a procedure.')

INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (16, N'Reporting', N'Haroon', N'SSRS Reports Development in Simple Terms', N'2019-07-10 22:26:42', N'The article is dedicated to the fundamentals of SQL Server Reporting Services (SSRS) development and aimed at beginners and professionals interested in database development.')

SET IDENTITY_INSERT [dbo].[Article] OFF

Create Full-Text Catalog

Create Full-Text Catalog using the script below or using the setup wizard:

-- Create Full-Text Catalog
CREATE FULLTEXT CATALOG ArticleCatalog AS DEFAULT;
GO

Define Full-Text Index on Notes

Once the catalog is created successfully, define a Full-Text Index based on the Notes column as follows (or by using the setup wizard):

-- Define Full-Text Index
CREATE FULLTEXT INDEX ON dbo.Article(Notes)
KEY INDEX PK_Article
WITH STOPLIST = SYSTEM;
GO

Full-Text Catalog and Full-Text Index Check

Expand related nodes to see Full-Text Index and Full-Text Catalog you have just created:

Now you are ready to run Full-Text queries against the table of interest – in our case, it is Article.

Simple Term

Searching for a word or a phrase is easier than searching for other forms, so it’s also known as a simple term.

Refer to the Implementing Full-Text Search in SQL Server 2016 for Beginners article to implement a basic form of a Full-Text query to search for a word or a phrase.

Now let’s discuss some of the potential advanced searches made using Full-Text Search.

Advanced Full-Text Search using Prefix Term

A prefix term refers to a prefixed word starting with some letters – it can then be anything that matches that pattern. You can think of it as a Full-Text Search version of LIKE with prefixedword*.

A word or words within a phrase can also have prefix term(s).

Example

For example, searching for the word data* can get us anything starting with data in that column such as data, database, databases, etc.

Dev-Related Articles Search using Prefix Term

Let’s imagine, you are tasked to search for development-related articles to organize them in a separate development group for better website viewing experience.

The solution is ‘prefix term search’ using a Full-Text query as follows:

--Implementing Prefix Term Full-Text Seach to find dev related articles
SELECT Title,Notes FROM dbo.Article WHERE CONTAINS(NOTES,'"dev*"')

The results also showed the article about DevOps – it is not dev-related, that’s why we run another script. It will find all dev-related articles quicker using a Full-Text query, excluding DevOps as follows:

--Implementing Prefix Term Full-Text Seach to find dev related articles but not DevOps
SELECT Title,Notes FROM dbo.Article WHERE CONTAINS(NOTES,'"dev*"') AND NOT CONTAINS(Notes,'DevOps')

The output is as follows:

Also note, it is essential to put the search word in double-quotes as shown in the scripts above. By doing this, we implement the prefix term search properly.

Advanced Full-Text Search using Inflectional Form

Sometimes you need to search for inflectional forms of a word that means possible forms a word can take.

Example

A simple example is the word “Deploy” which can have the following inflectional forms:

1. Deploys
2. Deployed
   
3. Deployment
   
4. Deployments
   
5. Deploying

Inflectional forms of Write Search 

Let’s see what happens if we run a Full-Text query to find Notes for all the inflectional forms of the word write:

-- Searching inflectional forms of word write using Full-Text Search

SELECT Title,Notes FROM dbo.Article WHERE CONTAINS(NOTES,'FORMSOF(INFLECTIONAL,write)')

The Notes column with rows containing both write and writing are shown where writing is an inflectional form of write.

Inflectional forms of Technology Search 

If you are tasked to find all articles containing different forms of the technology word, then implement an inflectional form of the word search using the following T-SQL script:

-- Searching inflectional forms of word technology using Full-Text Search
SELECT Title,Notes FROM dbo.Article WHERE CONTAINS(NOTES,'FORMSOF(INFLECTIONAL,technology)')

The output is as follows:

No Prefix Term Unit Search

Let’s now construct a more complex Full-Text query. It will search for all articles to find an inflectional form of the word test but without prefix term unit (excluding unit testing) as we are looking for the articles about testing, not about unit testing:

-- Searching inflectional forms of word test excluding unit prefix term using Full-Text Search
SELECT Title,Notes FROM dbo.Article WHERE CONTAINS(NOTES,'FORMSOF(INFLECTIONAL,test)') AND NOT CONTAINS(NOTES,'"unit*"')

As a result, we are going to see only records where manual, automated, or just testing words are mentioned excluding unit testing articles.

Advanced Full-Text Search using Proximity Search

Let’s use our hands-on experience of using proximity search by running a Full-Text query.

Proximity Search/Term

This form of Full-Text Search allows you to search for words or phrases that are close to each other. It’s very handy to find records when some words or phrases are located very tight.

Example

A good example of a proximity search is to search for records where the word football is near the word ground to get information about football grounds only.

Search for ‘advanced near testing’ using Proximity Term

If you want to search for all the articles where advanced testing is mentioned without following any particular order, your best option is to use proximity search given that Full-Text Search is set up against the desired column.

Write the following script to search for the word advanced near the word testing in any order in the Notes column:

-- Search for word advanced near word testing using proximity search (Full-Text Search)
SELECT NOTES FROM dbo.Article WHERE CONTAINS (NOTES,'NEAR(advanced,testing)')

The results are as follows:

The results showed us the notes where testing is mentioned by advanced level methods and this is what we were looking for. We can also specify the distance between two words.

Searching with not more than 2 words apart

Finally, run a Full-Text query using proximity term to find out every database scenario where the word database is near the word scenario but not more than 2 words apart.

-- Search for word database near word scenario not more than 2 words apart using proximity search (Full-Text Search)
SELECT NOTES FROM dbo.Article WHERE CONTAINS (NOTES,'NEAR((database,scenario),2,TRUE)')

The output is as follows:

As a result, the article where database development scenario is mentioned has been shown successfully.

With this, you have successfully learned to write advanced Full-Text queries against any column. This helps you meet sophisticated search requirements for being ready to run Full-Text Search in your professional life scenarios wherever applicable.

Things to do

Now that you can write advanced Full-Text queries, you should try these things to improve your skills:

1. Try to search for all the articles where the prefixed word auto is used.
2. Keeping in mind this article, try to write a script to do proximity search to find all rows where the word modern is near the word tools.
3. Try to find all inflectional forms of the word wrote.