Way of searching 30,000 SQL Records

I am about to make a simple search facility on my website, where a user will enter around 2-4 keywords which will get searched in two columns in a table in my MS SQL database. One column is a varchar (50) called title and one column is a varchar(2500) called description. There will be about 20,000-30,000 records potentially at any one time to search.

The keywords will need to return “the best matches” – you know the kind you get on search pages like ebay that return the closest matches. The way I was thinking of doing this is seems kind of naive – I thought I can read all 30,000 records of the table into and object like this:

  • Does ADODB fail on output parameters with optional input parameters?
  • Insert multiple records
  • Connection between J2me and Sql Server 2008
  • Rollover sum with grouping in SQL Server
  • Create Trigger for Insert
  • “Prevent saving changes that require the table to be re-created” negative effects
  • public class SearchableObject
        string Title {get; set;}
        string Description {get; set;}
        int MatchedWords {get; set;}

    Then create a List of that object e.g List go through all 30,000 records, populate the List, find out the ones that match most times and return the top 10 using something like

     if Description.contains(keyword1);

    But then find out how many times it occurs in the string to populate the MatchedWords field.

    My question is, is this the best way to do this? If not, what would be?

    6 Solutions collect form web for “Way of searching 30,000 SQL Records”

    full-text index search will do the trick.


    You should use a full text indexing solution. MS SQL Server 7 and later has a full text indexing engine built in (here’s a decent overview article). You could also consider using external products such as Lucene (available for Java and C#/.NET).

    i think you only want to use C# to parse the search parameters, not actually perform the searching and aggregation… So no, it’s not really the best way. Use SQL Server to do the search heavy-lifting.

    take a look at lucene for .net, that will allow full index of your text.


    the .net developers on this site may be able to tell you if there are any better alternatives

    If you’re working with Java or C#, I’d recommend Lucene or Lucene.NET respectively.

    Use a full-text search engine such as Lucene. There exists also a .NET version.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.