Full Text Search In Sql Server 2005

admin    Google     2005, Full, Search, Server, Text

I was working on my second site recently. It provides a warehouse of projects, presentations and other resources. To enables users to find the relevant project i decided to put in a search box. Initially all seemed 5 min job. I quickly wrote a simple SELECT query and thought that job is done. But then i realized that it isnt as easy as it appears. Many beginners will find it hard to achieve a google like search in their site. There are two ways to include a search feature in your site.

1. Use google to embed a search box in your site. This will enable surfers to search your site as well as googles.

2. Use full text feature of SQL server to enable users to search content of your site.

Now the problem with the first solution is that it provides user an option to drift away from your site. Also the search results can be customized only to certain extent.

I prefer the second solution for real developers. After a bit of searching i found a simple way to achieve this. Using full text feature you can get a google like search box working on your site. Lets get to the code:

step 1. first create a database you want to use

create database database_name

step 2. use the database you created above

use database_name

step 3. create the table with one primary key

create table table_name(

column_name1 datatype constraints,

column_name2 datatype constraints,

column_name3 datatype constraints,

….

)

Following commands are TSQL commands and runs inbuilt SQL procedures

step 4. enable fulltext search for the database

EXEC sp_fulltext_database  ‘enable’

step 5. create a new catalog for the table you want to search

EXEC sp_fulltext_catalog ‘catalog_name’,'create’

step 6. create a unique index. the index must be made on a unique column(preferrably the primary key) of the table you want to search. Note: index have max size of 900 bytes so a comination of columns using more than 900 bytes can cause problem in updation and selection

CREATE UNIQUE INDEX index_name ON table_name(unique_column_name);

step 7. create fulltext search

EXEC sp_fulltext_table ‘table_name’,'create’,'catalog_name’,'index_name’

step 8. add columns you want to search. you can add multiple columns

Exec sp_fulltext_column ‘table_name’,'column_name’,'add’

step 9.full text index tracking can be set to auto, manual  or off. in auto mode whenever there is a change in database the index updates itself. in manual  mode you need to start updation. in this real time updation is not possible. you can manually update using following command

EXEC sp_fulltext_table ‘table_name’, ’start_full’

you can off the change tracking when you dont want the index to b updated(this itself defies the logic of fulltext search but in certain condition you may want to switch it off). to put the change tracking on auto mode use

ALTER FULLTEXT INDEX ON uploadedfiles SET CHANGE_TRACKING AUTO

step 10. this is the last step. you need to activate the search.

EXEC sp_fulltext_table ‘uploadedfiles’,'activate’

Now the table is ready fo searching. but for searching you cannot use normal select query of types :         Select * from table_name where column_name LIKES ‘% QUERY %’. this again looks for exact match.

to search occurrence of multiple words we need to use CONTAIN command

select * from table_name where contain(column_name,’query’)

Above command search for query in mentioned columns but it doesnt require words to be present in same order eg. if you search for ‘cat and mouse’ and if column contains ‘mouse and cat’ then the row will be returned but if all the words in query are not present in same column then row wont be returned like for row containing ‘mouse and elephant’ wont be returned. for success of such queries you need to search words separately using AND

Select * from table_name where contain(column_name,’word’) and contain(column_name,’word’)

Exec sp_fulltext_column ‘uploadedfiles’,'description’,'add’

And thats all. now you will be able to search your site on multiple columns.

To check a working example visit http://www.academia.in

———————————————————————————————————————

download free projects, presentations, ebooks, papers. share with your friends, sms your buddies only@ http://www.academia.in