By: Manvendra Singh | Updated: 2021-05-27 | Comments (1) | Related: > Full Text Search
Problem
If you need to do wildcard searches on data that has a large amount of text and character-based data then regular indexing does not always provide adequate performance. There is an additional feature in SQL Server known as "Full Text Search" which is designed to address such requirements and this article covers the basics and generic steps to manage full text search objects.
Solution
Indexes are used to speedup query performance during read operations. There are many types of indexes available in SQL Server to improve query performance. All these indexes are inefficient if you need to run wildcard type text search queries to fetch character-based data from a SQL Server tables. There is one operator, LIKE, that allows you to do wildcard text searches, but the negative side of the LIKE operator is that it can be slow if we run it against a table that has large amount of text data.
SQL Server Full Text Search - Basic Concepts
Microsoft has developed a feature called Full Text Search to fill this gap and speedup performance of such queries that are used to fetch character-based data from tables that have a large amount of text data. If you are using the LIKE operator, then it might take more time whereas you will get the same output much faster using Full Text Search and its functionalities.
Full Text Search is implemented as follows:
- Install Full Text Search feature during installation or in existing installation
- Create Full Text Catalog to store full text indexes
- Create Full Text Index on tables or index views
- Write a Full text search queries using CONTAINS or FREETEXT operators to search specific words or strings
If you have not installed this feature during installation, then you can run the SQL Server setup to add this feature to an existing installation. Full Text Search feature does not install by default during SQL Server installation, you need to select it separately as additional feature during installation.
All databases will be enabled by default to use full text search after installation and any new database that will be created on that instance will have this feature enabled by default. Once this component is installed, you can create a full text catalog that works as a container to store all full text indexes. If you want to check whether Full Text Search is installed in your SQL Server instance, then you can run the below T-SQL statement:
--Check whether Full text Search is Installed or not
SELECT
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [Product Version],
SERVERPROPERTY('IsFullTextInstalled') AS [Full Text Search Installed];
GO
If the output value of server property "IsFullTextInstalled" returns a 1 it means this feature is installed on your SQL Server. Read more about getting server level properties using the system function SERVERPROPERTY().
There is another way to check whether this feature is installed in existing SQL Server Instance by checking Windows services. If this component is installed on you server then there will be a service called "SQL Full-Text Filter Daemon Launcher" as shown in the below screen. This service is created during full text search installation.
Now, let’s discuss how full text search works and process the queries. To understand this first we need to know about its architecture.
The architecture of full text search depends on two processes:
- SQL Server Database Engine Process (sqlservr.exe)
- Filter Daemon Host Process (fdhost.exe)
If you correlate these two processes with their respective services, then sqlservr.exe can be aligned with SQL Server database engine service and fdhost.exe with SQL Full-Text Filter Daemon Launcher service. You can see these both services in the above screenshot. As the full text search feature depends on both processes, these services must be running for full-text indexing and full-text querying to work.
Both processes have multiple components to serve the full text search query requirement. The SQL Server process uses the below components for full text search:
- SQL Server Query Processor
- Full text Engine
- Index Writer
- Filter Daemon Manager
- User Table
- Full Text Gatherer
- Thesaurus Files
- Stoplist Objects
Another process Filter Daemon Host also has multiple components that are used for full text search:
- Protocol Handler
- Filters
- Word Breakers and Stemmers
The full text engine is a very important component that handles end to end full text search processing. The full text portion of a query is performed by the full text engine. When a full text crawl is initiated, the full text engine (one of the SQL Server Process Components) pushes large batches of data into memory and invokes the filter daemon host for further processing. The filter daemon host is a process that is started by the Full-Text Engine. It runs the above full-text search components, which are responsible for accessing, filtering, and word breaking data from tables, as well as for word breaking and stemming the query input. Additional processing may be performed to remove stopwords, and to normalize tokens before they are stored in the full-text index or an index fragment. When a full text crawl has completed, a final merge process is triggered that merges the index fragments together into one master full-text index.
Managing SQL Server Full Text Catalogs and Full Text Indexes
Let's explorer full text search related objects in SQL Server Management Studio. I assume you have already installed full text search on your SQL Server instance. Connect to the SQL Server Instance using SQL Server Management Studio. You can see the Full Text Catalogs folder under "Storage" folder inside your "DatabaseName". You can expand the "Databases" node in SSMS and then expand the specific database icon for which you are checking. Finally expand the "Storage" folder to get the "Full Text Catalogs" folders. If you want to create a new full text catalog then you can right click at this folder and choose the "New Full-Text Catalog…" option, but if you want to check existing full text catalogs then just expand the folder full-text catalogs under the storage node.
Once you have created a full text catalog then you can create a full text index for any table. Select the table for which you want to create the full text index. Right click on the table and select the "Full-Text Index" option, you will get another window to select "Define Full-Text Index…" option. Click this option to start creating a full-text index. You will get a wizard to create this index, just follow the wizard and enter appropriate values as needed. You can create only one full-text index on a table. A full-text index can be created on one or more character-based columns which have the below data types:
- Char
- Varchar
- Nchar
- Nvarchar
- Text
- Ntext
- Image
- Xml
- Varbinary
- FILESTREAM
I will describe various generic use cases of the full text search feature that we do in our day to day job, like checking whether a database is enabled for full text search or not, how many catalogs are there in the database, etc. Although all databases are enabled for full text search after installing the full text search component, there is a possibility that somebody might have disabled this feature on a database and you will get the below error when checking the full text catalog.
Msg 15601, Level 16, State 1, Procedure sp_help_fulltext_catalogs, Line 7 [Batch Start Line 2] Full-Text Search is not enabled for the current database.
You can validate this by accessing the sys.databases table. There is a column "is_fulltext_enabled" in the sys.databases system table which we can see whether it is enabled for each database. If the output for your database returns a 1 it means full-text catalog is enabled whereas 0 shows it is disabled on the database.
--Check whether Full text Search is enabled on databases or not SELECT name as [DBName], is_fulltext_enabled FROM sys.databases GO
You can see all user databases are configured to use the full text search feature in the below screen.
Let me show you the default behavior of the full text search feature. I will create a new database and show whether the full text feature is enabled by default for this database. Have a look at below screen, I have created a database "Full_Text_Search" and then executed the query to check whether full text is enabled for this new database and we can see full text is enabled for the database.
There are various system tables and views that support the full text search feature in SQL Server. They provide metadata and configuration information about all full text search related components. As an example, you want to check all full text catalogs created on a database. We can use one of system table sys.fulltext_catalogs to get this information. I have executed the below code on the database to get this detail.
--Check all Full text catalogs in a database SELECT * FROM sys.fulltext_catalogs GO
Here is the output.
One aspect of the full text search feature in SQL Server is it supports more than 50 languages. You can get a complete list of supported full text languages by accessing another system object sys.fulltext_languages. Each of the columns contained in the full-text index is associated with a Microsoft Windows locale identifier (LCID) that equates to a language that is supported by full-text search. For example, LCID 3082 equates to Spanish, and LCID 4100 equates to Chinese (Singapore).
--Check all supported full-text languages SELECT * FROM sys.fulltext_languages GO
You can see the output has two columns. One is LCID and the other is its respective language name that supports full text search.
Microsoft has a lot of useful system stored procedures to manage and access details about full text catalogs and indexes. Although most of these systems stored procedures are going to be deprecated in future versions of SQL Server and Microsoft suggests using the advanced functions and T-SQL statements to fulfill your requirements, but you can use some of them to get the details like I have demonstrated in below screenshots.
The below example shows all tables for which full text index is defined. We use system procedure sp_help_fulltext_tables to get this data. This system stored procedure returns a list of tables that are registered for full-text indexing.
Here, you can see similar details for full text catalogs using another system stored procedure sp_help_fulltext_catalogs. From here, you can get how many tables have full text indexes under this catalog, you can see it is showing 3 tables in the below screen.
Next Steps
Explore the Full Text Search feature if you have any requirement to get character-based data from a table. Stay tuned for more articles on this topic where I will be enabling this feature at instance and database level.
- Read more articles about Full Text Search
- Check out alternatives:
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-05-27
