Why do we need Full Text index on view if the underlying table is already indexed

user_315493 0
Viewed 1654

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.

full-text-search sql-server-2016 sql-server
1 Answers

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'))
user_3154930
Related