“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
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 Search2. 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
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.
Configure Full Text Search for a particular table with in a database by following the below steps:2.1. Create a Full-Text CatalogOnce 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 IndexOnce 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.
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!
To search inside the binary files we need IFilters, in this case we will only install for .pdf and MS Office files.
Adobe PDF iFilter 9 for 64-bit platforms:
Adobe PDF IFilter v6.0 32-bit platforms:
Microsoft Office 2010 Filter Packs
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;goRestart 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:
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.
Full-Text Query Architecture