MSSQL Full-Text Search Configuration and Check

Full-Text Search (FTS) is an advanced feature in Microsoft SQL Server that allows for more robust and flexible querying of text data. This post details how to verify if Full-Text Search is installed and offers tips for setup.

Verifying Full-Text Search Installation

Run the following query in SQL Server Management Studio (SSMS) to check if Full-Text Search is installed on your server:

SELECT SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;

Output

• 1: Full-Text Search is installed.
• 0: Full-Text Search is not installed.

Using PsExec to Launch SSMS with System Privileges

In scenarios where you need elevated privileges to manage SQL Server, you can use PsExec

PsExec.exe -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

• PsExec.exe: A lightweight utility from the Sysinternals suite, useful for running processes with system-level privileges.

• Update the path to match the location of your SSMS executable.

Common Full-Text Search Tasks

1. Enable Full-Text Search

Ensure the Full-Text Search feature is installed during the SQL Server setup. If it’s not installed, rerun the SQL Server installation and select the Full-Text and Semantic Extractions for Search feature.

2. Create a Full-Text Catalog

A catalog serves as the container for full-text indexes

CREATE FULLTEXT CATALOG MyCatalog AS DEFAULT;

3. Create a Full-Text Index

Set up a Full-Text Index on a table or column

CREATE FULLTEXT INDEX ON MyTable(MyColumn)
KEY INDEX PK_MyTable
ON MyCatalog;

4. Query with Full-Text Search

Use CONTAINS or FREETEXT for flexible querying:

SELECT *
FROM MyTable
WHERE CONTAINS(MyColumn, 'search_term');

Example: Full-Text Search in Action

1. Check if Full-Text Search is installed:

SELECT SERVERPROPERTY('IsFullTextInstalled');

2. Create a Full-Text Catalog:

CREATE FULLTEXT CATALOG MyCatalog;

3. Index a column:

CREATE FULLTEXT INDEX ON Products(ProductDescription)
KEY INDEX PK_Products
ON MyCatalog;

4. Query the data:

SELECT *
FROM Products
WHERE CONTAINS(ProductDescription, 'laptop');

Troubleshooting

• FTS Not Installed: Ensure that the Full-Text feature was selected during the SQL Server installation.

• Service Issues: Verify that the SQL Full-text Filter Daemon Launcher service is running.