Skip to content
14/07/2010 / Danresa Consultoria de Informática

Installing FullText Search on SQL Server 2008

1. DownloadSQL Express with FullText Search capability for SQL Server 2008
http://www.microsoft.com/express/Database/InstallOptions.aspx

2. Select Installation Type as New Installation

3 During Feature Selection, select Full-Text Search

4. While Instance Configuration, Create new Instance Configuration.
– Select Named Instance and give new name.  Example: I have given name: MyShriGanesh

5. Main steps for installation are done, now simply press next with default choices and complete the installation.

6. After Installation login with new instance name created.
– Example: Earlier I have created named instance MyShriGanesh, so i need to login with same named instance in order to take benefit of FullText Search feature.

7. Now, Create New Database, Run Tables and SPs Scripts and restore database with data.  If you don’t have one already, download sample database and use that database.

After installation of sample database your object explorer will look like as follow:

8. Open Query Window and type following command to test whether FullText Search is installed or not.

Select SERVERPROPERTY(‘IsFullTextInstalled’)
If this query returns a value of ‘1’ then the component is installed.

Now, lets go step by step to run Full Text Search on SQL Server 2008

Step 1: Creating Full Text Catalog in SQL Server 2008
Syntax:
Create FullText Catalog DatabaseNameCatalog

Example:
Create FullText Catalog AdventureWorksCatalog

Step 2: Create FullText Index in SQL Server 2008
Syntax:
Create FullText Index on TableName
(Column1, Column2, …., ColumnN)
Key Index PK_TablesPrimaryKey on DatabaseNameCatalog
with Change_Tracking Auto
Note:
  • TableName is name of table you would like to create fulltext index.
  • ColumnName is column on which you would like to create fulltext index, it is column you would like to perform search on.
  • PK_TablesPrimaryKey is primary key of table you are creating on fulltext search index.
  • DatabaseNameCatalog is fulltext search catalog created earlier.
Example:
Create FullText Index on Production.ProductDescription
([Description])
Key Index PK_ProductDescription_ProductDescriptionID on AdventureWorksCatalog
with Change_Tracking Auto

 

Step 3: Running FullText Search Query
There are many different examples of running fulltext query, I would be giving 2 examples of fulltext search query.
Example 1: Using FreeText

Select [Description]
from Production.ProductDescription
Where
FREETEXT([Description],  ‘Entry Level Bike’)
















Example 2: Using Contains
Select [Description] 
from Production.ProductDescription
Where 
Contains([Description],  ‘”Entry” and “Level” and “Bike”‘)















Example 3: Using Weight keyword
 
Select [Description] 
from Production.ProductDescription
Where 
Contains([Description],  
‘ISABOUT (Entry Weight(.8), Level Weight(.4), Bike Weight (.2))’)

 

 

Example 4: Using Inflectional keyword

Select [Description]
from Production.ProductDescription
Where
Contains([Description],
‘FormsOf (INFLECTIONAL, Entry, Level, Bike)’)

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: