How to configure and use Full Text Search

How to configure and use Full Text Search

Full-Text Search in SQL Serverlets users and applications run full-text queries against character-based data in SQL Server tables. Before you will run full-text queries on a table, the DBA must create a full-text index on table. The full-text index includes one or more character based columns in a table. These columns can have any of the following data types: char, nchar,nvarchar,varchar ,text,ntext,image,xmlor varbinary(max) and FILESTREAM.
In order to configure full text search for content search file extension must be stored in table we can create column named as Extension with data type varchar.
Here is a step by step explanation guide for installing and configuring Full Text Search:
STEP 1:
Check full text search feature is installed or not for SQL server configuration manager:

Capture

If not installed you will be required to install full text search feature from installer:

Capture

STEP 2: Install Full-Text Search IFilters

To search inside the binary files we need IFilters, in this case we will only install for .pdf and MS Office files.

Adobe PDF

Adobe PDF iFilter 9 for 64-bit platforms download link:
http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025
Adobe PDF IFilter v6.0 32-bit platforms download link:
http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611

MS Office

Microsoft Office 2010 Filter Packs download link:
http://www.microsoft.com/download/en/details.aspx?id=17062

STEP 3: Regress all resources

EXEC sp_fulltext_service ‘update_languages’;
EXEC sp_fulltext_service ‘load_os_resources’, 1;
EXEC sp_fulltext_service ‘restart_all_fdhosts’;
EXEC sp_fulltext_service ‘verify_signature’, 0

STEP 4: Set Environment Variable
To set environment click on my computer properties and then Advanced Settings

Capture
Then click on Environment Variables in Advanced tab

Capture
Then Click on Select path and Edit and append the path of Pdf filter

Capture
And then append path C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin

STEP 5: Create Catalog and Index
SQL Query to create Catalog and Index
CREATEFULLTEXTCATALOGASDEFAULT;

CREATEFULLTEXTINDEXON
(HashValueTYPECOLUMNExtension) KEYINDEX ON WITHCHANGE_TRACKINGAUTO;
Example:

CREATEFULLTEXTCATALOGFileStreamCatalogASDEFAULT;
CREATEFULLTEXTINDEXONdbo.[File]
(HashValueTYPECOLUMNExtension)
KEYINDEXPK__File__3214EC072DFEEEDD
ONFileStreamCatalog
WITHCHANGE_TRACKINGAUTO;

STEP 6: Test Full text index working
SELECT*FROM[dbo].[File]WHEREFREETEXT(HashValue,’button’)
SELECT*FROM[dbo].[File]WHEREContains(HashValue,’Online’)

Tags:
  • Full Text Search,
  • How to use Full Text Search,
  • Leave a Reply