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
SET NOCOUNT ON;
FROM MyTable myTable(nolock)
INNER JOIN FreeTextTable(dbo.MyTable, Keywords, @Keywords) AS searchTable
ON myTable.MyPrimaryKeyID = searchTable.[KEY]
ORDER By searchTable.[RANK] DESC
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.