Full Text Search Step by Step Tutorial

Download and Install AdventureWorks for SQL Server 2008

Download AdventureWorks database: AdventureWorksDB.msi in Codeplex

Then double click your .msi file to Install your database.

By default it will install your database in
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your database will not appear automatically in your Object Explorer.

Go to Databases, right click then choose Attach...


If your database does not appear yet, refresh your Databases folder in the Object Explorer of your SQL Server Management Studio. To view your Object Explorer go to your menu bar then go to Views/Object Explorer or press F8.

Then you will see among your databases AdventureWorksDW.

Enable Full Text Search in Database

Create a new Full Text Catalog



A window will pop up to prompt for more details:
Full Text Catalog Name: FTCatalog

Create a new Full Text Index

Install Full Text Search
If your option is blocked (if it is not clickable) then re-run your SQL Server 2008 Installer. Under Installation (below Planning, on the left panel), choose New SQL Server stand-alone isntallation or add features to an existing installation.

Install Support Files.

Under Installation Type, choose Add features to an existing instance of SQL Server 2008.

Under Feature Selection check Full-Text Search.

Click Next then complete the installation. This will take around 5 minutes.

A Wizard window will appear after choosing Define Full-Text Index...

Follow the directions of the Wizard: choose the Primary Key for Indexing, then select all tables to enable full-text queries on all of them. Then choose the FTCatalog that we created previously when prompted. Skip Define population schedules for this tutorial. Then hit Finish.

Populate Index


Query Scripts - Test Full Text Search

Let's test our Full Text Search through the following SQL scripts:


On the new Query window, copy and paste the following scripts:

USE AdventureWorks
GO

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing OR Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing AND Assistant');
GO

Then Execute or F5 to run the query.


Notice that the results of the first SELECT query and the second SELECT query are the same.

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing OR Assistant');

The FREETEXT( ) query searches all job titles with either the string "Marketing" or "Assistant". The results are arranged according to their foreign keys.

By using the "AND" constraint on the third SELECT, only job titles with exact matches as the query string showed.

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing AND Assistant');

The arrangement of the results still depend on the foreign key (EmployeeID).

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

Source: http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

How to copy SQL table content into INSERT INTO statements in SQL Server using SQL CLR

Most of the time during the database development stage of a specific system, programmers concerned with the different aspects of the system work all at the same time. While the Database Admin fine tunes the data structure including its constraints, metadata, relationships, views, stored procedures, the Data Access Layer Man has already started working on his end of the fence. He determines the anchor or the point of access, which table would most efficiently access all other related information. He makes sure that when data is inserted, deleted, updated then all other related information, all other related tables to that specific data are updated appropriately.

While the Data Access Layer is working on those delicate and logic intensive information, he has to have access to the database. However, the Database Admin is still working on the database itself. Since the Data Access Layer especially when done in an Entity Data Model (.NET Entity Framework) is sensitive to the stability of the information in the database, it is not wise if they were to connect to the same database.

In most business practices, the Data Access Layer team/man is given only a copy of the original database being developed. When a significant change was made to the database, he rebuilds his Entity Data Model accordingly. This is a tough job but it’s the only way to work at the same time with the DBA and therefore deliver faster. Sometimes the DAL would request the DBA to insert sample data within the database so that he may test his scripts against the database. But since they are physically connected to different databases, the DBA fixes SQL Script of Insert statements for him.

On the occasion when the DAL actually inserts his own table rows and the Web Dev man sees that there is already enough structure for him to connect his application and to test some of its units, he would need a copy of that database. It easy to script the database structure including its triggers and content to create it in a different local machine, however, when the database is already running in a different local machine and all the Web Dev needs is the rows to check if he can successfully display the table rows, let’s not waste time dropping and recreating databases.

Click to download code.

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; using System.Collections.Generic; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CopyContentOfTable(String tablename) { using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; StringBuilder sb = new StringBuilder(); sb.Append("SELECT column_name FROM information_schema.columns ") .Append("WHERE table_name = '").Append(tablename).Append("'"); cmd.CommandText = sb.ToString(); //List down all the colum names in the table List<String> columnames = new List<String>(); SqlDataReader reader; using (reader = cmd.ExecuteReader()) { if( reader.HasRows ) while (reader.Read()) { columnames.Add(reader["column_name"].ToString()); } } //get all data from table sb.Length = 0; sb.Append("SELECT * from ").Append(tablename); cmd.CommandText = sb.ToString(); //define needed logical objects for creating the insert statement String columnvalue = String.Empty; bool notfirstcolumn = false; bool notfirstrow = false; using (reader = cmd.ExecuteReader()) { sb.Length = 0; //Enable inserting identity, //we want to make sure that the databases are as identical as possible sb.Append("SET IDENTITY_INSERT ").Append(tablename).AppendLine(" ON;"); //you may turn off any related triggers if you wish to here... //Build our insert statements sb.Append("INSERT into ").Append(tablename).AppendLine(" ("); //list down all column names foreach( String column in columnames) { if(notfirstcolumn) sb.Append(","); else notfirstcolumn = true; sb.Append(column); } //add each row, result from the previous SELECT transaction notfirstcolumn = false; sb.AppendLine(") VALUES "); if (reader.HasRows) while (reader.Read()) { if (notfirstrow) sb.Append(","); else notfirstrow = true; sb.Append("("); foreach (String column in columnames) { if (notfirstcolumn) sb.Append(","); else notfirstcolumn = true; columnvalue = reader[column].ToString(); if (columnvalue.Equals(String.Empty)) sb.Append("NULL"); else sb.Append(columnvalue); } sb.AppendLine(")"); notfirstcolumn = false; } //re-disable indentity inserts sb.Append("SET IDENTITY_INSERT ").Append(tablename).Append(" OFF;"); } //Your insert statement will appear in the Message tab of the query window. //Copy and paste it onto the query window and run SqlContext.Pipe.Send(sb.ToString()); } } };