Full Text Search on files in SQL Server

What is Full Text Search in SQL Server?

Full Text Search in SQL Server enables us to perform complex queries against character based data. Full Text Search supports char, varchar, nchar, nvarchar, text, ntext, image, varbinary and xml data types. We Can store document files in varbinary(max) format with their extensions and enable Full-Text search on it. SQL Server supports many types of document files for Full-Text indexing some of them are .asp, .aspx, .ascx, .c, .doc, .html, .ppt, .txt, .xls and many more. Some other extension like .pdf provides it’s own “Filter Pack” to enable Full-Text index on pdf files in SQL Server.

To enable Full Text Search on one or more columns of the given table or indexed view, we have to create a Full Text Index on it. After creating a full text index, we can perform word searching or phrase searching on a column based on the rules of the given language. We can create only one Full text index on a table or indexed view but this index can accommodate 1024 columns and in turn each column can have specific language. Lets see this in action.

To see all supported file types for Full-Text Search, use below query;

SELECT * FROM sys.fulltext_document_types

Before proceeding with Full-Text index creation, create a demo table as below;

Create a table as below

CREATE TABLE TestFTS
(
Id INT IDENTITY(1,1) PRIMARY KEY,
FileName VARCHAR(100),
Extension VARCHAR(50),
Content VARBINARY(MAX)
)
GO

Remember that you have created a unique index on your table or view before proceeding for Full-Text index creation. it can be a primary or unique key on this table or view as like above.

Creating Full-Text Index with Full-Text catalog

Follow these steps to create a Full-Text index to perform Full-Text Search in SQL Server on a document file:-

• Open object explorer and go to the created table.
• Right click on it.
• You will get a pop up window as below. Go to “Full-Text index” and click on “Define Full-Text Index” as in below figure.

• “Welcome to the SQL Server Full-Text Indexing Wizard” welcome screen will appear, click on “Next” button.
• On next screen “Select an Index”, select the unique key and click on “Next” button again.

•  On next screen “Select table columns”, from available columns, select those columns on which you want to create a Full-Text index. In our demo we are selecting “Content” column. Also select the TypeColumn for this column. In type column we put the extension of the file like “.docx”. Here i am selecting “Extension” column. We can also select the default “Language for word breaker” for this column. Click on “Next” button.

•  On “Select Change Tracking” screen, select “Automatically” and click on “Next” button.

•  On “Select catalog, Index Filegroup, and Stoplist” screen, check the “Create a new catalog” checkbox and put a name here. In our case it’s “DOCCatalog”. Click on next button.

•  On “Define population schedules (optional)” screen, click on next button.

•  On “Full-Text Indexing Wizard Description” screen, click on Finish button.

•  You will get a success message as below.

Now upload your file in the table. In our table we have data as like below;

SELECT * FROM TestFTS

Querying Full-Text Index

Now put a query like below which will extract all the fields from table which has “Hello” in it’s word file content;

SELECT * FROM TestFTS WHERE CONTAINS(Content, 'Hello')

You can also try with FREETEXT instead of CONTAINS like below;

SELECT * FROM TestFTS WHERE FREETEXT(Content, 'Hello')

Full-Text Search not working on document files .docx

In case after doing all the above steps, still you are not able to perform Full-Text search on your table. Follow these steps in sequence.

1. Download “Microsoft Filter Pack 2.0” from Microsoft. Click Here.
2. Execute below commands in sequence as they are;

EXEC sp_fulltext_service 'update_languages';
EXEC sp_fulltext_service 'load_os_resources', 1;
EXEC sp_fulltext_service 'restart_all_fdhosts';

In above commands, we are updating the language after Filter Pack install using sp_fulltext_service ‘update_languages’, also refreshing the filters using sp_fulltext_service ‘load_os_resources’, 1 and finally restarting the FTS service using sp_fulltext_service ‘restart_all_fdhosts’.

So, in this post, we have learned how we can create and utilize a Full-Text Index for Full-Text Search on a table or indexed view. Please don’t forget to put your comments and suggestions in comment section and do share this on your social media if it really helps you and rate it too.