Best Way To Partial Search in SQL 2008

I've looked into SQL 2008's built-in Full-Text search, and also Lucene.NET.. but I don't think they'll do what I need to do. And I just want to make sure I'm building my program as efficient as possible.

So here's the dream. I want to have a single textbox on a page (like google) and开发者_开发知识库 allow the user to enter ANYTHING in. And based on their text, I will search 10's of tables to find what they're looking for.

Example. My database contains thousands of locations, each of which have multiple names / codes. Within each location, there is tonnes of data associated with them.

So if the user wants to display all the locations with the codes that contain "VM" ("CD-VM01", "CD-VM02", "CD-VM03", etc).. they should be able to. Or if they want to find all the locations in Toronto, they just type Toronto.. I want to make the search as easy as possible for people. (I've found that people don't like thinking)..

Plus it ends up being easier to scale to more search options if I can just search the database, and not have to add new fields to a search screen.

So if I don't use Full Text search (which I can't for partial) the only thing I can see that i'm left with is "Like" .. is that right? is that my only option?

---

I guess the question is, even if you were able to do this in the database, how would you handle it in the UI?

Most likely every search result from a different table will have different attributes that need to be displayed in order for the end user to understand what it is.

The Google search box only needs to search one thing - the content of web pages - and return one type of result - web page URLs and excerpts. Fundamentally you are trying to search for many different things, and so you'll most likely need to handle each case separately.

Alternatively, you could maintain a denormalized search table that contains only the search text and the common attributes you think need to be displayed with each hit. Maintain it either with a scheduled task or with triggers. You'd be able to use FTS on this as well.

Update

Some of the comments express some uncertainty over what SQL Server Full-Text Search is capable of. FTS can most definitely search for a single string anywhere within the text of a column, and can do other things as well (proximity search, free-text search, etc.) If you're just getting started then I'd recommend the TechNet pages on the subject, the documentation is very comprehensive.

In particular I'd suggest having a look at the section on Configuring Catalogs and the Getting Started page (Cole's Notes: you have to create catalogs - writing CONTAINS queries without them won't get you very far). Then take a look at the querying page. I'd be very surprised if you can't find answers to any and all of your questions there.

If you still can't get it to work, I would post a new question with the specifics of your problem - what you've tried, what you're expecting, and what's happening instead.

---

I believe Lucene does exactly what you're looking for. You can add an index from any external data source (including multiple database tables), then query that index and you'll get back pointers to the matching records.

The drawback is that unlike with full-text indexing, you're responsible for building and maintaining the index yourself.

You can see an example of how Lucene.NET might be used.

---

It appears that the easiest / quickest solution for this exact problem would be to use LIKE.