Implementing Full-Text Search in SQL Server 2016 for Advanced Users

Total: 2 Average: 3.5

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.


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:

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 Full-Text Catalog

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

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):

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).


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:

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:

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.


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:

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:

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:

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.


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:

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.

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.

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
Haroon Ashraf