What is full text indexing? 1

[JazzLeg]

Hi,

I am working on a search for a knowledge. I have heard people mention full-text indexing in SQL server.

In brief, could anyone how this works?

Many thanks

 

[osjohnm]

Hi

Full-text indexing works together with Microsoft Search Service. You select a table and then setup a full-text index on a the field you wish to search for. It creates a catalog which is re-populated as often as you set it up to be.

You can then query character data using the CONTAINS or FREETEXT functions.

For example if you have field call product_name and you want to search for all the products to do with computers
it would look like this...

...where product_name like '%computers%' which will return any record which is similiar

but 'PC', 'Workstation', 'Desktop' are also computers so you would have to use multiple OR statements which are very slow.

with free-text all you do is the following...

..where CONTAINS(product_name, 'computers or pc or desktop or workstation')

this is much quicker.

follow these instructions on how to setup a full-text index:

Right click on your categories table > full-text index table >
define full text indexing > use the setup wizard to select the primary key/unique index > select the column where your names or descriptions are(yours would be categoryname) > name the catalogue > you can setup a schedule to re-populate > click finish.

Then under the database go to full-text catalogues, right click > start full population. That will popultate the catalogue. It is a good idea to re-populate you catalogue once a day with the scedule as any data added to the table after the catalogue was created will not exist in the catalogue until it is re-populated.

Also read up on Full Text Search in Books Online.

John

 

[JazzLeg]

Thanks very much for your reply, things are now starting to make sense.