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';
You can user any of the predicate and function to query the FTS
FREETEXTTABLE are useful for different kinds of matching
Follow the link to Read More About Full-Text Search