Using Full Text Search in SQL Server 2008


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,
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


SELECT * FROM sys.fulltext_languages ORDER
BY name ASC


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

Once the  full-text search feature is successfully installed
on the  server you will be able to see SQL Full-text Filter Daemon
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

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

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


Pages: 1 2 3


No comments yet... Be the first to leave a reply!