SQL Server 2008 FullText Search Query

Question

Hello, I have added Full Text Search to my sql server 2008 express database and created an index catalog for two columns in a single table. So now, I have to rewrite one of my stored procedures but I have no idea where to begin. The following is my current SP that I need to convert to take advantage of the full text search capability:

ALTER PROCEDURE [dbo].[sp_page_GetPostsBySearchFront]
(
 @Title nvarchar(256), 
 @Content nvarchar(MAX), 
 @startRowIndex INT,
 @maximumRows INT
) 
AS 
BEGIN
SELECT 
  RowNumber, 
  postId, 
  Title, 
  Content, 
  DateCreated, 
  IsPublished, 
  PublishOnDate, 
  Type, 
  MenuName 
FROM 
(
SELECT ROW_NUMBER() OVER (ORDER BY posts.postId DESC) AS RowNumber, 
  posts.postId, 
  posts.Title, 
  posts.Content, 
  posts.DateCreated, 
  posts.IsPublished, 
  posts.PublishOnDate, 
  posts.Type, 
  posts.MenuName 
FROM posts 
GROUP BY 
  posts.postId, 
  posts.Title, 
  posts.Content, 
  posts.DateCreated, 
  posts.IsPublished, 
  posts.PublishOnDate, 
  posts.Type, 
  posts.MenuName 
HAVING (posts.Title LIKE N'%' + @Title + N'%') 
OR (posts.Content LIKE N'%' + @Content + N'%') 
AND (posts.IsPublished = 1) 
AND (posts.PublishOnDate <= GETDATE()) 
) as u
WHERE u.RowNumber > @startRowIndex 
AND u.RowNumber <= (@startRowIndex + @maximumRows) 

END

Could some one explain how I go about accomplishing this task? Do I use CONTAINS or FREETEXT and where do I add it. I'm just lost on this? Thank you!

Answer 1

Use:

WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY p.postId DESC) AS RowNumber, 
           p.postId, 
           p.Title, 
           p.Content, 
           p.DateCreated, 
           p.IsPublished, 
           p.PublishOnDate, 
           p.Type, 
           p.MenuName 
      FROM POSTS p
     WHERE (   CONTAINS(p.title, @Title)
            OR CONTAINS(p.content, @Content))
       AND p.IsPublished = 1
       AND p.PublishOnDate <= GETDATE() )
 SELECT u.RowNumber, 
        u.postId, 
        u.Title, 
        u.Content, 
        u.DateCreated, 
        u.IsPublished, 
        u.PublishOnDate, 
        u.Type, 
        u.MenuName 
   FROM cte as u
  WHERE u.RowNumber > @startRowIndex 
    AND u.RowNumber <= (@startRowIndex + @maximumRows)

Too bad you have different parameters for searching the title and content fields - could've been consolidated into a single CONTAINS if the parameters are the same value.

The documentation provides a nice breakdown of when they suggest Full Text Search (FTS) functionality. The majority of examples suggest CONTAINS/CONTAINSTABLE, and use FREETEXT/FREETEXTTABLE for:

• The inflectional forms are the different tenses of a verb or the singular and plural forms of a noun.
• A thesaurus defines user-specified synonyms for terms.

Answer 2

OK, Here is what I did to make it work. The reason why I was receiving the noise word message is because I did not set the parameter value length @search correctly. Once I realized that and changed it, it works fine.

ALTER PROCEDURE [dbo].[sp_page_GetPostsByFTS] ( @search nvarchar(255), @startRowIndex INT, @maximumRows INT ) AS BEGIN SELECT RowNumber, postId, Title, Content, DateCreated, IsPublished, PublishOnDate, Type, MenuName FROM ( SELECT ROW_NUMBER() OVER (ORDER BY posts.postId DESC) AS RowNumber, posts.postId, posts.Title, posts.Content, posts.DateCreated, posts.IsPublished, posts.PublishOnDate, posts.Type, posts.MenuName FROM posts WHERE FREETEXT((Title,Content),@search) AND (posts.IsPublished = 1) AND (posts.PublishOnDate <= GETDATE()) ) as u WHERE u.RowNumber > @startRowIndex AND u.RowNumber <= (@startRowIndex + @maximumRows) END