Full-search text query

Question

I want to add simple full-search-text to my webapp, that can accept multiple words to search.
Example search entry: "Online SQL books"

Should I transform it to:

WHERE (CONTAINS(myColumn,  '"Online*" and "sql*" and "books*"' ))  
or : WHERE (CONTAINS(myColumn,  '"Online* sql* books*"' ))  
or maybe: WHERE (CONTAINS(myColumn,  '"Online*"' ))  and (CONTAINS(myColumn,  '"SQL*"' ))  and (CONTAINS(myColumn,  '"books*"' ))

or maybe it's better to use 'FORMSOF(INFLECTIONAL('...

Another question where it's better to construct the query, inside store procedure on inside my business object and then pass it to the stored procedure?

Answer 1

The first form is well (CONTAINS(myColumn, '"Online*" and "sql*" and "books*"' )) - sql anyway spent time to parse query.

Second question is matter of convenience. If you well with string manipulation in sql2005, then do it. But remember on sql-injection problem, so check twice.

Answer 2

The problem with using SQL for full text search is that it can be both very slow and misses searches that you would otherwise want it to succeed on. SQL searching also does not provide any form of ranking to the data so any text containing the terms could end up near the top.

To counter these two problems Lucene.net was created to allow the simple creation of an appropriate word to entries index. In doing so it can also see how frequently words are used within particular entries and provide ranking information. You will find it dramatically outperforms using SQL for the task, both in terms of finding results and execution speed on single searches and especially when scaling to support more searches.