Execute Complex Search Queries with Ranked Results in SQL Server

By: Jeremy Kadlec | Updated: 2007-10-17 | Comments | Related: > Full Text Search

Problem

Just like every other company, we need to have our queries run as fast as possible for our users to have the best experience possible with our application. One of the core components of our application is to search across a number of columns in a table. We have been trying to accomplish this task with a variety of T-SQL options and nothing has been very quick. To add fuel to the fire, we need to rank the data so that the most relevant data is sorted from top to bottom. I have seen some of your recent tips related to Full Text Search. Can this technology help me achieve high performance searching while ranking the data?

Solution

As a matter of fact, the Full Text Search implementation in SQL Server 2005 has the ability to query across a number of columns in a single table then return the results in a sorted manner. This is achieved by using the CONTAINSTABLE command where a table of results are returned with the associated rank for each row. The table that is returned via the CONTAINSTABLE command has a column named KEY that contains the Full Text index key values corresponding to the relational table unique key value. In addition, the resulting table has a column named RANK which is a value from 0 to 1000 for each row indicating how well a row matched the selection criteria. From that point forward, ORDER BY and WHERE clauses can be added to the original SELECT statement to fine tune the result set. Let's walk through some examples to see if these will meet your needs.

CONTAINSTABLE Syntax

CONTAINSTABLE ( table , { column_name | (column_list ) | * }
 , ' < contains_search_condition > ' 
     [ , LANGUAGE language_term] 
  [ ,top_n_by_rank ] 
          ) 
< contains_search_condition > ::= 
    { < simple_term > 
    | < prefix_term > 
    | < generation_term > 
    | < proximity_term > 
    |  < weighted_term > 
    } 
    | { ( < contains_search_condition > ) 
    { { AND | & } | { AND NOT | &! } | { OR | | } } 
     < contains_search_condition > [ ...n ] 
    }
< simple_term > ::= 
          word | " phrase "
< prefix term > ::= 
     { "word * " | "phrase *" } 
< generation_term > ::= 
     FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) 
< proximity_term > ::= 
     { < simple_term > | < prefix_term > } 
     { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] 
< weighted_term > ::= 
     ISABOUT
        ( { { 
  < simple_term > 
  | < prefix_term > 
  | < generation_term > 
  | < proximity_term > 
  } 
   [ WEIGHT ( weight_value ) ] 
   } [ ,...n ] 
        )

Example 1 - Sorted result set with OR logic

In this example, all of the columns in the Production. Product table configured for Full Text Search are queried and only those columns with a rank over 100 are returned in descending order. As you can see, the relational table and full text table are joined via the unique key in the relational table (FT_TBL.ProductID) and in the full text catalog (KEY_TBL.[KEY]).

USE AdventureWorks;
GO
SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK
FROM Production.Product AS FT_TBL 
INNER JOIN CONTAINSTABLE(Production.Product, *,
 '"*washer*" OR "*ball*"') AS KEY_TBL
ON FT_TBL.ProductID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 100
ORDER BY KEY_TBL.RANK DESC
GO

Example 2 - Sorted result set based on the most relevant values

The change from example 1 is a literal phrase 'flat washer' is queried with only the 10 most relevant results are returned, which is the parameter specified after the literal phrase in the CONTAINSTABLE command.

USE AdventureWorks;
GO
SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK
FROM Production.Product AS FT_TBL 
INNER JOIN CONTAINSTABLE(Production.Product, *, 
'"flat washer"', 10) AS KEY_TBL
ON FT_TBL.ProductID = KEY_TBL.[KEY]
GO

Example 3 - Proximity search

Search the records where the terms 'XL', 'men' and 'shorts' are all in close proximity.

USE AdventureWorks;
GO
SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK
FROM Production.Product AS FT_TBL 
INNER JOIN CONTAINSTABLE(Production.Product, *, 
'XL NEAR men NEAR shorts', 10) AS KEY_TBL
ON FT_TBL.ProductID = KEY_TBL.[KEY]
GO

Example 4 - Prefix search

The only difference in this query from the previous example is to search for records with the 'chain' prefix.

USE AdventureWorks;
GO
SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK
FROM Production.Product AS FT_TBL 
INNER JOIN CONTAINSTABLE(Production.Product, *, 
'"chain*"', 10) AS KEY_TBL
ON FT_TBL.ProductID = KEY_TBL.[KEY]
GO

Example 5 - Pass in parameters to the Full Text query

Below is an example where the query parameters and the percentage are returned based on the parameters passed into the code.

USE AdventureWorks;
GO DECLARE @Parm1 varchar(50)
DECLARE @Parm2 int
SET @Parm1 = '"XL" OR "men" OR "shorts"'
SET @Parm2 = 10 
SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK
FROM Production.Product AS FT_TBL 
INNER JOIN CONTAINSTABLE(Production.Product, *, 
@Parm1, @Parm2) AS KEY_TBL
ON FT_TBL.ProductID = KEY_TBL.[KEY]
GO

Next Steps

Check out the code examples and see if these will meet your needs to have a high performance search solution with ranking across a single table.
For guidelines on how to setup Full Text Search in your environment, check out this tip.
Check out these related tips:

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2007-10-17