SQL Server FullText search on non-persisted columns

by Diego Jancic   Last Updated September 12, 2018 22:06 PM

Let's say I have a table with 3 columns: Id, ColA and ColB. Then I created a FullText index:

CREATE FULLTEXT INDEX ON [Table]
(
    ColA LANGUAGE 1033,
    ColB LANGUAGE 1033
) KEY INDEX [PK__table_id]

However, I figured that if I do a query like this: ... WHERE CONTAINS(*, '"this" AND "that"') it will return only results that contain the words "this" and "that" in the same column (ie, either ColA contains both words or ColB contains both words, but "this" in ColA and "that" in ColB won't match).

As a solution I found I can do this:

ALTER TABLE [Table] ADD
    Combo AS [ColA] + CHAR(32) + [ColB]

CREATE FULLTEXT INDEX ON [Table]
(
    Combo LANGUAGE 1033
)

Notice that the computed column is NOT persisted.

The question is: is there any drawback with having a full-text index on a non-persisted computed column? I couldn't find any information related to this and don't have the resources to do performance tests other than in production. But it seems to work completely fine in my tests.

The only reference I found to this is in MSDN and but it doesn't say much.



Related Questions