Jon's Blog

The FreeTextTable function in SQL Server is very useful when you need to search a column for words, phrases, etc. FreeTextTable will return a table that you can join on.  It also returns the Rank of the matches so you can order the most relevant matches at the top.

In the example below I am searching the Keywords column of the MyTable table using the @Keywords passed into the stored procedure.  I am then ordering them by Rank so the highest matches show up first.

CREATE PROCEDURE [dbo].[FreeTextSample]
    @Keywords [varchar](1000) = ''
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    SELECT myTable.MyPrimaryKeyID,
        myTable.Title,
        myTable.Description
    FROM MyTable myTable(nolock)
    INNER JOIN FreeTextTable(dbo.MyTable, Keywords, @Keywords) AS searchTable
        ON myTable.MyPrimaryKeyID = searchTable.[KEY]
    ORDER By searchTable.[RANK] DESC

END
GO    

One thing to note is that LINQ does not contain an equivalent keyword for FreeTextTable.  So what I did was to create a new method on my DataContext that returns the results of the stored procedure.  See my post here for further details on how to do this.