Using NOT keyword with full-text search and multiple columns

Question

Is it possible to use the CONTAINSTABLE and NOT keywords with SQL Server full-text searching and exclude rows where a single column contains the value to be excluded?

For example, let's take the following indexed view (simplified for the purposes of illustrating this problem):

ItemId INT
FirstName VARCHAR(200)
MiddleName VARCHAR(200)
LastName VARCHAR(200)
Address VARCHAR(1000)
ChildrenNames TEXT
SearchData TEXT

SearchData is a concatenated field generated from the other text/varchar fields.

This view can then be searched using the following query to find rows that contain firstname but not lastname:

SELECT  *
FROM    [v_MyView] V
        LEFT OUTER JOIN CONTAINSTABLE
        (
          [v_MyView],
          (
           [FirstName],
           [MiddleName],
           [LastName],
           [Address],
           [ChildrenName],
           [SearchData]
         ), '"name" AND NOT "lastname"') FTS ON [FTS].[Key] = [V].[ItemId]
 WHERE   (ISNULL(RANK,0) > 0)

This query doesn't seem to return the correct results, as it will only exclude rows if ALL of the fields being searched meet the criteria, whereas it should exclude ANY rows that meet the criteria.

ie. Rows that contain name AND lastname in any (single) column should be excluded from the result set, rather than only rows that contain name and lastname in every column (which is going to be unlikely).

The only option available seems to be to check if the user query contains any exclude values (eg. name -lastname), and if so, restrict the query to searching the 'SearchData' column only as this is a concatenated column and will contain all data. However, this will affect the relevancy ranking and doesn't seem like a good solution.

Answer 1

I have run into the same issue, I suspect this is a bug in SQL server.

I suspect that someone forgot !( A & B & C) === !A | !B | !C

For now I would make a new column to store the concatenation of all the rows you want to search, add triggers to add all the text to your new column when data gets inserted or updated, and just search off that one new column.

This de normalizes your table, but seems to preform reasonably well.