搜索关键字并检索找到它的列?

创建包含所有相关列的视图。然后查询该视图。

它将有助于显示特定的数据值和预期结果。

或者,您可以使用带有联合的结构,并指示行来自哪个联合。与此类似:

select name, 1 from person where name like '%civic%'
union
select street,2 from address where street like '%civic%'

…

等

我不确定其他选项的性能如何,但是当我们需要这种类型的功能时,我们创建了一个全文目录。您可以在SSMS的[数据库名称]-存储-全文目录下找到此功能。定义要索引的字段,然后让它生成。然后您可以对索引进行查询。

这是一篇很好的文章来补充我糟糕的描述: http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm

我使用全文索引以获得更好的性能:

SELECT
    A.UserId,
    CASE WHEN CONTAINS(C.nm_colaborador, @palavraChave) THEN CAST(1 as bit) ELSE CAST(0 as bit) END As Nome,
    CASE WHEN CONTAINS(C.dc_cidades, @palavraChave) THEN CAST(1 as bit) ELSE CAST(0 as bit) END As Cidade
FROM
    Curriculo A
    INNER JOIN CONTAINSTABLE(Curriculo_Busca, *, @palavraChave) B ON A.UserId = B.[KEY]
    INNER JOIN Curriculo_Busca C ON B.[KEY] = C.UserId