2

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.

2

There isn't any kind of drawback with having a full-text index on a non-persisted column. Creating an index and putting a non-persisted column, the column will be automatically persisted on disk. You can try by yourself creating a nonclustered index and putting a non-persisted column. After this, you can use the command DBCC PAGE for looking the entire data of the nonclustered index and you will see the non-persisted column there.

Have one thing on mind. the non-persisted column stills non-persisted on the clustered-index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.