How to make full text query with AND when text spans several columns

We have table with several full text indexed fields. Say, it is firstName, surName and lastName. I'm looking for "John AND Smith" and obviously these two words most likely will be written in different fields. Query returns nothing. If I search for "John OR Smith" it is working.

The question is: can I somehow tell SQL Server that all fields belonging to one row of specific table should be treated as one continuous text fragment?

Of course, I can concat fields manually to special field and index it, but it looks quite silly.

Just i开发者_开发百科n case, the query. It's not about person's name, but idea is the same:

SELECT [id], [type], [accessClass], [rank] 
FROM ( 
SELECT DISTINCT temp_result.* 
FROM ( 
    select SDF_Article.article_id [id], 
    6 [type], SDF_Object.accessClass [accessClass], RelevanceTable.[rank] [rank] 
    from SDF_Article inner join SDF_Object SDF_Object 
    on SDF_Article.article_id=SDF_Object.object_id 
    inner join SDF_Article_Locale SDF_Article_Locale 
    on SDF_Article.article_id=SDF_Article_Locale.article 
    inner join ContainsTable(SDF_Article_Locale, (title, body, brief),
    'FORMSOF (INFLECTIONAL, hello) AND FORMSOF (INFLECTIONAL, world)', Language 'Russian') RelevanceTable 
    on SDF_Article_Locale.entry_id=RelevanceTable.[KEY] 
    ) AS temp_result 
INNER JOIN SDF_RelationMap SDF_Map 
ON temp_result.[id] = SDF_Map.object AND SDF_Map.ancestor = 5 ) 
AS result ORDER BY [rank] desc

We are using SQL Server 2005.

One solution is to create an auto populated column that concats the columns you wish to index and search. Then point the full text indexer at this column. Then you just form your query around the new column.

It turns out that there is no support for this in SQL Server. Even more, such behavior was "fixed" in 2004: http://support.microsoft.com/default.aspx?scid=kb;en-us;294809

I added suggestion in MS Connect. Please support: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=526862