2

I have a query which is fairly complex and refers to a joined table-pair a number of times. To simplify the query and improve performance, I've pulled that table pair out into a sub query.

However, now my full text index doesn't work, stating that CTE_Query.Indexedfield is not full text indexed.

Super simplified example query below

WITH CTE_Query AS
(
    SELECT ID, IndexedField
    FROM IndexedTable it
        INNER JOIN SomeOtherTable sot
        ON sot.itid = it.id
    WHERE blahblah...
)
SELECT CTE_Query.ID, CTE_Query.IndexedField
FROM CTE_Query
WHERE CONTAINS(CTE_Query.IndexedField, 'Some search term')
UNION ALL
SELECT CTE_Query.IndexedField
FROM CTE_Query
WHERE CONTAINS(CTE_Query.IndexedField, 'Another search term')
-- This part of the query is complex and re-uses the CTE_Query and other tables a number of times, I've simplified it here

When executing, I get the following error

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

This suggests that SQL Server loses track of the fact that this just an alias to a full text indexed field.

Is there any way to force SQL Server to apply a specific full text index here? Or is this just part of how a full text index works? I guess it's likely that the index relies on having access to the original table, but I'm not sure how it works underneath

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jon Story
  • 2,881
  • 2
  • 25
  • 41
  • I am a little surprised, given that SQL Server doesn't materialize CTEs. However, I think you need to chalk it up to how full text works. – Gordon Linoff Mar 12 '19 at 17:36
  • Yeah I was surprised too, I expected it to be plug-n-play, with SQL Server just tracking the field through the CTE. I'm still living in hope that there's some kind of override to contains that lets me specify the index and field – Jon Story Mar 12 '19 at 17:43

0 Answers0