Installing Full-Text Search with SQL 2017

Full-Text Search in SQL Server and Azure SQL Database lets users and applications run full-text queries against character-based data in SQL Server tables.

Installing Full-Text Search

SQL Server Full-Text Search feature is an optional competent of the Database Engine and doesn’t get installed by default.While installing SQL Server you have to select the Full-Text Search Feature as shown below

On Successful installation of the feature you will see SQL Server Full-text filter Daemon Launcher services in the SQL Server Configuration Manager. Make sure this service is started before attempting to use Full-Text Search.

Configure  Database for Full-Text Search

Expand the database and navigate to Storage – Full Text Catelogs

  • Create a Full Text Catalog :  Right click Full Text Catalogs and select New Full-Text Catalog

Provide the name of the Full-text catalog,owners and select the option as applicable. Click OK to save.

Create a Full-Text Index

Before we create a Full-Text Index ,make sure the column data type is set to BLOB Data type (Image,Var-binary) and Allow NULL is checked.In this example I am using varbinary(max).

Now create Full-Text Index on the table which will use the Full-Text Catalog.

Navigate to Storage – Full Text Catalogs – select the Catalog- Right Click and select properties.

Select Tables/Views from the Properties Page. Select the table from the Table List and move it to the assigned to to catalog list.

Select the Index and check the Table is full-text enabled check box.

Under Eligible Columns, select the column storing the PDF/ Document etc. and in the Data type Column choose extension (In this article I am only using PDF and the extension column will have .pdf). Under Track changes choose option Automatic.

Now Click OK.

T-SQL to create Full-Text Index

USE [AdventureWorks2012]
GO
CREATE FULLTEXT INDEX ON [dbo].[Customer_pdf] KEY INDEX [PK_Customer_pdf] ON ([ReadPDContent]) WITH (CHANGE_TRACKING AUTO)
GO
USE [AdventureWorks2012]
GO
ALTER FULLTEXT INDEX ON [dbo].[Customer_pdf] ADD ([content] TYPE COLUMN [extension])
GO
ALTER FULLTEXT INDEX ON [dbo].[Customer_pdf] ENABLE
GO

Installing Full-Text Search IFilters

To search through the binary files ,IFilters is required.For PDF you need to install PDF ifilter.(for word docs you can download Microsoft office filter pack).

The current version for Adobe Ifilter is 11 , which is not compatible with SQL Server 2017.So,I am using version 9.

You can download pdf iFilter 9 from: ftp://ftp.adobe.com/pub/adobe/acrobat/win/9.x/

The file name is: PDFiFilter64Installer.zip

Once PDFiFilter installation completes successfully, execute the below command from Query Analyzer

To read more about sp_fulltext_service follow this link.

exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;

Restart the SQL Server Services

Now execute the below command to make sure PDFiFiler is correctly installed and can be accessed by SQL Server

exec sp_help_fulltext_system_components 'filter';

 

Query FTS

You can user any of the predicate and function to query the FTS

CONTAINS/CONTAINSTABLE and FREETEXT/FREETEXTTABLE are useful for different kinds of matching

Follow the link to Read More About Full-Text Search

https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-2017

 

Leave a Reply

Your email address will not be published. Required fields are marked *