Keyword search with SQL Server

Question

Hi,

I have a scenario where I need to search for cars by keywords using a single search field. The keywords can relate to any of the car's attributes for e.g. the make or the model or the body style. In the database there is a table named 'Car' with foreign keys referencing tables that represent models or makes or body style.

What would be the best way of doing this? Specifically, How should I take the query from user(must support exact phrase search, or, and) and how do I actually do the search.

I am using SQL Server and ASP.NET 3.5 (Data access using LINQ)

Answer 1

Easily the best and most comprehensive article on the subject : http://www.sommarskog.se/dyn-search-2005.html

Answer 2

Regardless of which implementation you pick from Aaron's article, I always log the search criteria and execution time in this situation. Just because you provide search flexibility, it doesn't mean most users will make use of it. You usally find most searches occur on a limited number of fields and logging the search criteria will allow you to create targetted indexes.