Full text search on calculated columns

2018-06-23 20:27:10

I need to allow full-text searching on two fields that are concatenated together. In this case, FullName = FirstName + ' ' + LastName. The simple approach seems to be to create a FullName calculated column then include that as a column in the full-text indexing. Is this an acceptable approach? How would you do it?

UPDATE and INSERT happens very rarely, SELECT using CONTAINS is very common by comparison. I'm using SQL 2008.

Sample table definition where FirstName, LastName, NickName and FullName are columns used in the full-text search:

TABLE Player
    Id uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid()),
    ...
    FirstName nvarchar(128),
    LastName nvarchar(128),
    NickName nvarchar(128),
    FullName  AS ([FirstName]+' '+[LastName]) PERSISTED

You can add a full text index to a view.

Create a view that includes your two columns concatenated. Make sure to create it with "schema binding":

CREATE VIEW [dbo].[v_MyView] WITH SCHEMABINDING etc..

If you are using Management Studio the rest of the process should be straightforward.

链接地址: http://www.djcxy.com/p/66848.html

上一篇: SQL Server 2008在包含复合主键的表上进行全文搜索

下一篇: 在计算列上进行全文搜索