question

donnyv avatar image
donnyv asked

How to use CONTAINS with inline queries in SQL2008?

I have this sql query where I'm trying to use CONTAINS to search the title field.

But I get this error.

"Cannot use a CONTAINS or FREETEXT predicate on column 'Title' because it is not full-text indexed."

The Titles table has been indexed and a CONTAINS works fine with a simple search.

Does anyone know what I'm doing wrong? Are CONTAIN queries not supported with inline queries?

This query is being ran in SQL2008

SELECT pi.PublisherGUID, pi.Publisher, pi.TitleGUID, pi.Title, 
  pi.YearsPublished, pi.FrontImage, pi.IssueGUID, pi.IssueNumber, 
  pi.IssueVariation, pi.IssueNotes, pi.CoverDate, pi.IsForSale 
  FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY PublicIssues.Title,PublicIssues.IssueNumber) AS RowNum, 
      PublicIssues.PublisherGUID, PublicIssues.Publisher, 
      PublicIssues.TitleGUID, PublicIssues.Title, 
      PublicIssues.YearsPublished, PublicIssues.FrontImage, 
      PublicIssues.IssueGUID, PublicIssues.IssueNumber, 
      PublicIssues.IssueVariation, PublicIssues.IssueNotes, 
      PublicIssues.CoverDate, PublicIssues.IsForSale
      FROM (SELECT dbo.tblTitles.PublisherGUID, dbo.tblPublishers.Name AS Publisher, 
            dbo.tblTitles.TitleGUID, dbo.tblTitles.Title, 
                dbo.tblTitles.YearsPublished, dbo.tblIssues.IssueGUID, 
                dbo.tblIssues.IssueNumber, dbo.tblIssues.IssueVariation, 
                dbo.tblIssues.IssueNotes, dbo.tblIssues.CoverDate, 
            dbo.tblStockIssueImages.FrontImage, 
                ci_owner.IssueForSale(dbo.tblIssues.IssueGUID) AS IsForSale
            FROM dbo.tblStockIssueImages RIGHT OUTER JOIN
                        dbo.tblIssues ON 
                        dbo.tblStockIssueImages.StockIssueImageGUID = dbo.tblIssues.StockIssueImageGUID 
                        LEFT OUTER JOIN
                        dbo.tblTitles INNER JOIN
                        dbo.tblPublishers ON dbo.tblTitles.PublisherGUID = dbo.tblPublishers.PublisherGUID 
                        ON dbo.tblIssues.TitleGUID = dbo.tblTitles.TitleGUID
                ) 
    AS PublicIssues
    WHERE 1=1 AND CONTAINS(Title,@xTitle)
  ) AS pi
WHERE RowNum BETWEEN (@xPageNum - 1) * @xPageSize + 1 AND 
@xPageNum * @xPageSize ORDER BY pi.Title
sql-server-2008full-text
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Matt Whitfield avatar image
Matt Whitfield answered

I'm not sure about whether or not CONTAINS / FREETEXT is supported in general through derived tables, but have you thought about either:

  • changing the left outer join to dbo.tblTitles to an inner join? You seem to be excluding rows that are not matches with your WHERE 1=1 AND CONTAINS clause anyhow, so surely you don't actually want a LEFT OUTER join?

or

  • moving the CONTAINS predicate within the derived table query? If you only want matches where contains returns true outside of the derived table, then surely you also only want matches where contains returns true inside the derived table?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.