Full Text Search in SQL Server 2008

Introduction

SQL Server 2008 Full-Text Search feature can be used by application developers to execute full-text search queries against character based data residing in  a SQL Server table. To use full text search the developer must create a full-text index for the table against which they want to run full-text search queries. For a particular SQL Server Table or Indexed View you can create a maximum of one Full-Text Index. The full-text index can be created for columns which use any of the following data types - CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT, VARBINARY, VARBINARY (MAX), IMAGE and XML.
Each full-text index can be used to index one or more columns from the base table, and each column can have a specific language which is supported by SQL Server 2008 Full-Text Search. Full-Text Search in SQL Server 2008 supports more than 50 different languages such as Arabic, Chinese, English, Japanese and Spanish etc.
For the complete list of supported full-text languages, run the   below   TSQL query.
Use master
GO

SELECT * FROM sys.fulltext_languages ORDER BY name ASC
GO

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 :


In case   Full-Text feature is not available on the server then the DBA can install this feature by running the SQL Server 2008 setup   again and  selecting the “New SQL Server stand-alone installation or add feature to an existing installation” option under Installation   (as  shown below).

If you are new to SQL Server 2008 and would like to know how to steps which you need to follow in order to install SQL Server 2008 then please review the Steps to install SQL Server 2008 article.
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.
Steps to Configure a Database for Full-Text Search
Configure Full Text Search for a particular table with in a database by following the below steps. In this article we will examine each step in detail.
  • Create a Full-Text Catalog
·         Create a Full-Text Index
·         Populate a Full-Text Index

Create a Full-Text Catalog

Once you are connected to the SQL Server 2008 instance expand Databases node >   AdventureWorks database > Storage . Right click Full Text Catalogs and select New Full-Text Catalog… from the drop down link as shown   below   to create a Full-Text Catalog for the  AdventureWorks database.


In New Full-Text Catalog screen, you need to mention the name as AdventureWorksFullTextCatalog for Full-text Catalog. Next you need specify DBO as the owner for this catalog and finally click OK to save and to create AdventureWorksFullTextCatalog Full-text catalog as shown in the below snippet.


Once the Full-Text catalog is created successfully, it will be visible under the Full Text Catalogs   shown below.



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 HumanResources.Employee table of the AdventureWorks database. Note that you can create only one full-text index on a particular SQL Server table.

1. Right click the HumanResources.Employee table and select Full-Text Index and   choose Define Full-Text Index… as shown   below   to open the Full-Text Indexing Wizard.



2. In the Welcome to the SQL Server Full-Text Indexing Wizard screen click Next to continue with the configuration.


3. In the Select an Index wizard screen, you will be shown a list of all the valid indices which are available for the HumanResources.Employee table under the Unique Index drop down list   shown   below . In this example select the PK_Employee_EmployeeID index and click   Next to continue with the wizard.


4. In the Select Table Columns screen, you will be shown a list of all the columns which have character-based or image-based data types and which are eligible for full-text queries. Select all the columns individually and also choose the desired Language for Word Breaker. In this example use English as the language for Word Breaker. Note that Full-Text Search in SQL Server 2008 supports more than 50 different languages. Click Next to continue.


5. In the Select Change Tracking wizard screen, select a change tracking method (see below). When you define automatic or manual change tracking, the full population of the index starts immediately once the wizard is complete. To avoid a full population at the end of the wizard you need to choose Do not track changes option and uncheck the  Start full population when index is created check box as shown below. Click Next to continue.


6. In the Select Catalog, Index File Group, and Stop List screen, select the AdventureWorksFullTextCatalog as the full text catalog, this is the catalog   created earlier. Select the index Filegroup as PRIMARY and leave rest of the settings unchanged and click Next to continue with the wizard.


7. In Define Population Schedules (Optional)   screen,   set the schedule to populate the full-text index. To manually populate the full-text index leave this screen as it is and click next to continue.



8. In the Full-Text Indexing Wizard Description screen, you will be able to see a quick summary of the options which you have selected so far. Click Finish to complete the configuration of the Full-Text Index.


9. You will be shown a success message once the full-text index is configured successfully :


Populate a Full-Text Index


The final step before you can start using Full-Text Search Queries is to Populate the Full-Text Index. This can be done by right clicking the HumanResources.Employee table and selecting Full-Text Index > Start Full Population     as shown   below.


You will be shown a  success message once the Full-Text Index Population is completed successfully:



Once the Full-Text Index is successfully created and populated the next step is to run   full-text search queries.   SQL Server 2008 provides a range of full-text predicates such as CONTAINS and FREETEXT as well as row set valued functions such as CONTAINSTABLE and FREETEXTTEABLES for writing full-text search queries.

FREETEXT Predicate

Use the FREETEXT predicate in a WHERE clause to search columns containing character based data types, this find matches for the meaning of the word(s) and not just the exact text in the search condition. When FREETEXT is used, the SQL Server Query engine internally assigns each term a weight and then finds the matches.

CONTAINS Predicate


Use the CONTAINS predicate in a WHERE clause to search columns containing character based data types for precise or less precise matches to a single words or phrases. If you want to combine different words within a search, then you need to use conditions such as AND, OR etc within the search queries.
Below   are some of the examples of  full-text search queries which can be run against theHumanResources.Employee table in AdventureWorks.
USE AdventureWorks
GO
SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Recruiter')
GO
SELECT TOP 5 EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS ([Title], 'Recruiter OR Manager')
GO


Is this the Work Around or Best Solution?


Yes this is Best Solution.

Comments

Post a Comment

Popular posts from this blog

Connecting Remote server with Local Server

Image
Introduction: Linked server is powerful functionality provided by SQL Server. We can create object of remote server on local server and use that object to perform operations on remote server. To explain this topic let's consider a scenario Scenario:   Local server having number of databases. We need to take backup of local databases and restored this database into the remote server. For this scenario we will follow certain steps: Step 1 : Create object of Remote Server Add remote server object in local server Syntax:     sp_addlinkedserver @server = N'LinkServer' ,     @srvproduct = N' ' ,     @provider = N'SQLNCLI' ,     @datasrc = N'Server Name ,     @catalog = N'Database Name' Here we have created object of remote server i.e. LinkServer . By using this object you can perform Select, Update functionality on the same. Step 2 : Grant Permission Next Step...
Read more

MDX Error handling tricks

Error List: Duplication of column value(column is not primary key): This issue occur due to NULL records. You have to handle NULL records for particular column. Below are the some properties which you need to set for the attribute: On dim property set: UnknownMember = Visible On attribute property:Key column Expand -> Null Processing-> handle in your way.
Read more

JSON_Automated_stored_procedure

Image
What is JSON: JSON stands for  J ava S cript  O bject  N otation, and is a way to store information in an organized, easy-to-access manner, "self-describing" and easy to understand. It gives us a human-readable collection of data that we can access in a really logical manner. Why we need JSON: JSON is a lightweight data-interchange format and it is language independent. Many sites are sharing data using JSON in addition to RSS feeds nowadays, and with good reason: JSON feeds can be loaded asynchronously much more easily than XML/RSS. Format of JSON string: JSON Example { "employees" :[     { "firstName" : "John" ,   "lastName" : "Doe" },     { "firstName" : "Anna" ,   "lastName" : "Smith" },     { "firstName" : "Peter" ,   "lastName" : "Jones" }             ] } You can compa...
Read more