views:

67

answers:

1

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 SQL Server 2008.

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
+2  A: 

Indeed, in the context of PublicIssues, Title is not full-text indexed.

It is indexed in the the table tblTitles.

I think it may be possible to move the CONTAINS predicate inside the expression which defines PublicIssues. Something like the following. However I suspect (with the hint of the 1=1) that the idea is to have various other criteria, and it may not be feasible to have all of them "inside". It being [apparently] dynamic SQL, it may be feasible to craft the query by placing the search criteria in one of the two locations as appropriate.

  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

             WHERE CONTAINS(Title,@xTitle)  --- this lined moved
            ) 
AS PublicIssues
mjv
To remedy this, could you just create the entire sub-query as a temp table and create a full-text index on that temp table, then select from that?
rwmnau
You could but: a) you would have to break the query in 3 (select/create temp table, FT index it, search from it) and more importantly, the creation of the FT index would likely be take longer than expected.
mjv
I guess SQLServer isn't smart enough to know that the field is indexed when used inside of a subquery. You think it would since you have to pick which fields you want indexed.
Donny V.