What's the best way to implement a search?

Question

I've got a requirement where a user enters a few terms into a search box and clicks "go". Does anyone have any good resources on how to implement a dynamic search that spans a few database tables?

Thanks, Mike

Answer 1

It is called Full-text Search.

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Answer 2

You can create a SP that receive the search terms as parameters and retun some "selects" (recordsets) to the program that launched. It can return a select for each table and you can do whatever you need with the data in your app code.

If you need to receive only a dataset, you can make a View using UNION of the tables for consolidate the columns in a common schema and then filter the view same way. You will receive in your application only a dataset with all the information consolidated in the view and filtered.

Answer 3

This is a pretty loaded question given the lack of detail. If you just need a simple search over a few tables/columns then a single (cludgy) search SP may be enough for you.

That said, if you need more features such as:

• Searching a large set of tables
• Support for large amounts of data
• Searching over forms of a word
• Logical operations
• etc

then you might want to look into Full-Text Search (which is a part of MS Sql 2000 and above). The initial investment to get up to speed with Full-Text Search can be a bit offsetting, but compared to implementing the above features you'll likely save yourself a ton of time and energy.

Here are some Full-Text Search links to get you started:

• Msdn Page
• Initial Set Up
• Set Up Video

Hope that helps.

Answer 4

Is the question here how to write a search function? If it's nothing too advanced I'd suggest reading up on weighted search and experimenting a bit.

Answer 5

I'm gonna throw in my vote for Lucene. While SQL Server does provide full text indexing and some search capabilities, it is not the greatest search engine. In my experience, it does not provide the best results or result ranking until you have a significant volume of indexed items (tens of thousands to hundreds of thousands minimum).

In contrast, Lucene is explicitly a search engine. It is an inverted index, behaving much like your run of the mill internet search engine. Lucene provides a very rich indexing and search platform, as well as some rich C# and .NET API's for querying the indexes. There is even a LINQ to Lucene provider that will allow you to query a Lucene index with LINQ.

The one drawback to using Lucene is that you have to build an index, which is a side-band process that runs independently of the database. You have to write your own tool to manage the index as well. Your search index, depending on how frequently you update it, may not be 100% up-to-the-minute up to date. Generally, that is not a huge concern, but if you have the resources, the Lucene index culd be incrementally updated every few minutes to keep things "fresh".

Answer 6

Ok there were a few requests for more info so let me provide some. I have several tables (ie. users, companies, addresses) and I'd like a user to be able to enter something like this:

"microsoft wa gates"

and bring up a result list containing results for "gates", "microsoft", and "washington".

Lucene seems like it could be pretty cool.