Simple Search result ranking algorithm for ASP / SQL Server

Posted on 2005-04-15
Last Modified: 2012-08-13
Hi all,

I am developing a website that enables visitors to search through a database of books - the search will query the author, title and keyword field.  As it is search mutiple fields, it would be useful to be able to rank the results by 'relevance' as do some search engines.

Does anyone know where I can get some good sample code for doing this?


Question by:BobFett
    LVL 9

    Expert Comment

    You could use some of the many freetext searches provided within SQL. Each of these will provide a Rank or a Weight against the search. This would give you your relevance.
    LVL 2

    Author Comment

    Hi Alfa,

    Thanks for your prompt response - not entirely sure what you mean?  Could you expand on this please?  Are these 'free text' searches inherent in SQL or do you mean SQL Server?

    LVL 9

    Accepted Solution

    SQL Server offers FREETEXT and CONTAINS methods. check them out in BOL (the help file), and see if they're of any use? MIght make your search terms more complicated, but does provided a relevance ranking already.
    LVL 10

    Assisted Solution

    You'd have to set up a "Full Text Catalog".

    Enable Full Text:

    IF (SELECT DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
          EXEC dbo.sp_fulltext_database N'enable'

    Setup a new catalog:

    EXEC dbo.sp_fulltext_table N'new_full_text_table_name', N'create', N'your_exisiting_table_name_here', N'your_existing_table_primary_key'

    Add columns from your table:

    EXEC dbo.sp_fulltext_column N'new_full_text_table_name', N'column_name_from_existing_table', N'add', 0

    Activate your FTC:

    EXEC dbo.sp_fulltext_table N'new_full_text_table_name', N'activate'

    You can then use searches by using the CONTAINSTABLE command to join a table onto your query which will return a column called rank. You will need to do some research into this but this bit of code will help you get started.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now