“Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types:char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM.
Full-text queries can include simple words and phrases or multiple forms of a word or phrase. A full-text query returns any documents that contain at least one match (also known as a hit). A match occurs when a target document contains all the terms specified in the full-text query, and meets any other search conditions, such as the distance between the matching terms.”Full-Text Query Architecture

Contents

This how-to will explain how to enable Full Text Search on your SQL Server database, and also how to configure it.
The main steps in this process are:
1. Installing Full-Text Search
2. Configure a Database for Full-Text Search

2.1. Create a Full-Text Catalog

2.2. Create a Full-Text Index

3. Installing Full-Text Search IFilters

3.1. Adobe PDF

3.2. MS Office

1. Installing Full-Text Search

The Full-Text Search feature of SQL Server 2008 is an optional component the Database Engine and as a result this feature is not installed by default. During SQL Server 2008 Installation, the database administrator needs to select the Full-Text Search feature as shown below:



Once the full-text search feature is successfully installed on the server you will be able to see SQL Full-text Filter Daemon Launcher service in the SQL Server 2008 Configuration Manager. Make sure this service is started before attempting to use Full-Text Search or the queries will fail.

2. Configure a Database for Full-Text Search

Configure Full Text Search for a particular table with in a database by following the below steps:

2.1. Create a Full-Text Catalog
Once you are connected to the SQL Server 2008 instance expand Databases node > YOU_DATABASE_NAME > Storage.



Right click Full Text Catalogs and select New Full-Text Catalog… from the drop down link as shown below:



In New Full-Text Catalog screen, you need to mention the name as YOU_DATABASE_NAME_FullTextCatalog. Next you need to specify DBO as the owner for this catalog. Click OK to save.

2.2. Create a Full-Text Index
Once you have successfully created a Full-Text Catalog, the next step is to create a Full-Text Index. In this example, we will be creating a Full-Text Index on the OSUSR_y9y_FTSDocumentContent table that has the following attributes:



Right click CONTENT attribute and select modify:



Check if CONTENT attribute have allow nulls set to true:



If so, we need to set it as false, and hit ctrl+s, a warning will show up telling which tables are being altered. Click Yes to continue.

SQL Server 2008, can’t save changes to tables

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes. You will get an error stating, “Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.” Solution: Unless you are using Change Tracking in SQL Server for custom data replication – You are completely safe.  Go ahead and do this: Tools > Options > Designers Simply uncheck “Prevent saving changes that require table re-creation”. NOTE: Your data will be copied from the old column definition to the new column definition.

Now that the attribute meets the requirements to be a full text index, in the design view, right click at CONTENT attribute and select Fulltext Index:



Click Add and then click at the small buttom “…”.



Now we will select which column to index as shown below:



Important Note: the Typed by Column should be set as the FILETYPE attribute and here we will store the file extension eg.: “.doc”,”.pdf”, ….
Hit ctrl+s to save all changes, and we are done!

*We have already data in the table

If there is already data in table we need to rebuild the full text index. Solution: Select and expand your table with the index > Expand Indexes > Right click on the index > Rebuild NOTE: This can take some time, depending on the existing data.

3. Installing 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.

3.1. Adobe PDF

Adobe PDF iFilter 9 for 64-bit platforms:

https://www.adobe.com/support/downloads/detail.jsp?ftpID=5542

Adobe PDF IFilter v6.0 32-bit platforms:

https://www.adobe.com/support/downloads/detail.jsp?ftpID=2611

3.2. MS Office

Microsoft Office 2010 Filter Packs

https://www.microsoft.com/download/en/details.aspx?id=17062
 

Download and install the filters, after that execute the threelines bellow on your SQL Server:
exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;
go

Restart SQL server. After that check if IFilters are installed (.pdf, .docx for example)
exec sp_help_fulltext_system_components 'filter';



Our database is now ready to search inside binaries, but how to do it?
Example of really simple FTS Query:

SELECT 
    {Document}.[FileName] 

FROM 
    {Document} 

WHERE CONTAINS([Content],'outsystems')

If our database is storing CV's, this way we can see all the CV's that contains the word Outsystems. To know more about syntax, visit the link Full-text Search in related resources section.

Related Resources (msdn)

Full-Text Query Architecture

Full-Text Indexes

Full-text Search

regards,

Miguel Antunes