Relevance Search in SQL 2000

This is a query that can be used to retrieve a relevance search from a SQL database. You will need to be using the full text catalog on the SQL server. You then can join with the FreeTextTable to get a ranking. You can order by the ranking to give your search results some relevance. The dbo.LibraryItemVersions is the table and the LIV_Content is a blob field in the LibraryItemVersions table. The 'Project Management Plan' is the item that you are searching on.

SELECT FT_TBL.LIV_LIGUID,FT_TBL.LIV_VERGUID,FT_TBL.LIV_URL,FT_TBL.LIV_FileLocation
,KEY_TBL.RANK
FROM dbo.LibraryItemVersions AS FT_TBL
INNER JOIN FREETEXTTABLE(dbo.LibraryItemVersions, Liv_Content,
'Project management plan')
AS KEY_TBL
ON FT_TBL.LIV_verGUID = KEY_TBL.[KEY]
order by rank desc;
GO