Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Execute Complex Search Queries with Ranked Results in SQL Server


By:   |   Read Comments   |   Related Tips: More > 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

 



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools