Scenario
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
Thanks for reading this article,
Next steps :
- Add this script to your database toolkit
- Share this with your colleagues because Sharing is Learning
- Comment below if you need any assistance