6

Let's say we have an Employees table with a full text index on a Note field. We can search that table using a query like this: SELECT ID FROM Employees WHERE CONTAINS(Note, 'scout')

However, if we create an EmployeesView with something as simple as SELECT ID, Note FROM Employees, we're not able to query that view with SELECT ID FROM EmployeesView WHERE CONTAINS(Note, 'scout')

That query would raise the following error:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'EmployeesView' because it is not full-text indexed.

I would like to understand why it isn't possible for a view to run a FTS against its underlying table like it does for other regular indexes?

Note #1 This question isn't about how to create a full text index on a view as this as already been answered. This question is to understand why we have to do it.

Note #2 Our database is more complex than the provided example. We have many views using the same full text indexed table, some can be indexed while others cannot because of the schema binding restriction. I also think it's a bit odd (and a huge disk space waste) to add a FTI to every single view if each of them would identical.

S3S
  • 24,809
  • 5
  • 26
  • 45
The_Black_Smurf
  • 5,178
  • 14
  • 52
  • 78
  • Out of curiosity, do you have `WITH VIEW_METADATA` defined on the `VIEW`? – Dai Aug 10 '22 at 18:06
  • Good point... I just tried `VIEW_METADATA` but it didn't help. – The_Black_Smurf Aug 10 '22 at 20:30
  • Actually, I asked because I thought that _having_ `VIEW_METADATA` might explain it (because using `VIEW_METADATA` actually _hides_ the `BASE TABLE` and other metadata from the rest of the system which I thought would explain why you had issues with full-text indexes on the view but not the table... but nvm :) – Dai Aug 10 '22 at 20:33

1 Answers1

1

It's not really an answer to my own question (I won't accept it) but for anyone who's looking for a workaround solution that would allow you to use full text search on a view without creating a FTI on it, you can do it by using an INNER JOIN between the table and the view:

SELECT * FROM EmployeesView INNER JOIN Employees ON EmployeesView.ID = Employees.ID
WHERE CONTAINS(Employees.Note, 'scout')

or by using a WHERE IN clause:

SELECT * FROM EmployeesView
WHERE ID IN (SELECT ID FROM Employees WHERE CONTAINS(Note, 'scout'))
The_Black_Smurf
  • 5,178
  • 14
  • 52
  • 78