SQL Server Full Text Search - Create one computed column

Question

Hi all,

I am currently working on a project where I want to search for employees via just one input search term. For this I am using the SQL FTS.

The table schema looks like this

Employee table

EmployeeId, Firstname, Lastname

Sample data

1, John, Miller

2, Chuck, Norris

---

Address table

AddressId, EmployeeId, CityId, Street, StreetNumber

Sample data

1, 1, 1, Avenue, 12

2, 2, 2, Wimbledon Rd, 12

---

City table

CityId, Name, ZipCode

Sample data

1, Hamburg, 22335

2, London, 12345

---

So now I got the following search term:

• John Hamburg: Means John AND Hamburg and should return 1 record.
• John London: Means John AND London and should return 0 records since there is no John in London.
• Norris Wimbledon: Means Norris AND Wimbledone and should return 1 records.

Now the problem with this is that using CONTAINSTABLE only allows to search one table at a time. So applying "John AND Hamburg" on the Employee Full text catalog returns 0 records since "Hamburg" is located in the address table.

So currently I can use "OR" instead of "AND" only, like:

SELECT
   (keyTblSp.RANK * 3) AS [Rank],
    sp.*
FROM Employee sp    
    INNER JOIN 
        CONTAINSTABLE(Employee, *, 'John OR Hamburg', 1000) AS keyTblSp
        ON sp.EmployeeId = keyTblSp.[KEY]    

UNION ALL
SELECT
   (keyTbl.RANK * 2) AS [Rank],
    sp.*
FROM Employee sp    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId 
    INNER JOIN 
        CONTAINSTABLE([Address], *, 'John OR Hamburg', 1000) AS keyTbl
        ON addr.AddressId = keyTbl.[KEY]    
UNION ALL
SELECT
   (keyTbl.RANK * 2) AS [Rank],
    sp.*
FROM Employee sp    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId 
    LEFT OUTER JOIN [City] cty ON cty.CityId = addr.CityId
    INNER JOIN 
        CONTAINSTABLE([City], *, 'John OR Hamburg', 1000) AS keyTbl
        ON cty.CityId = keyTbl.[KEY]  

This causes that not just John who lives Hamburg is returned, but every person named John and every person who lives in Hamburg. One solution I could think of is to somehow compute a column in the Employee Table that holds all necessary values for the full text search like.

Employee table

EmployeeId, Firstname, Lastname, FulltextColumn

Sample data

1 | John | Miller | John Miller Avenue 12 Hamburg 22335

So then I could do

SELECT
   (keyTbl.RANK) AS [Rank],
    sp.*
FROM Employee sp    
    INNER JOIN 
        CONTAINSTABLE([Employee], FulltextColumn, 'John AND Hamburg', 1000) AS keyTbl
        ON sp.EmployeeId = keyTbl.[KEY] 

Is this possible? Any other ideas?

Answer 1

you could use a join to require a match in both the address and the persons name.

SELECT
   (keyTblSp.RANK * 3) AS [Rank],
    sp.*
FROM Employee sp    
    INNER JOIN 
        CONTAINSTABLE(Employee, *, 'John OR Hamburg', 1000) AS keyTblSp
        ON sp.EmployeeId = keyTblSp.[KEY]    
join
(
    SELECT
       (keyTbl.RANK * 2) AS [Rank],
        sp.*
    FROM Employee sp    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId 
    INNER JOIN 
        CONTAINSTABLE([Address], *, 'John OR Hamburg', 1000) AS keyTbl
        ON addr.AddressId = keyTbl.[KEY]
UNION ALL
    SELECT
       (keyTbl.RANK * 2) AS [Rank],
        sp.*
    FROM Employee sp    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId 
    LEFT OUTER JOIN [City] cty ON cty.CityId = addr.CityId
    INNER JOIN 
        CONTAINSTABLE([City], *, 'John OR Hamburg', 1000) AS keyTbl
        ON cty.CityId = keyTbl.[KEY]  
) addr_matches
on addr_matches.EmployeeId = sp.EmployeeId

which I think would give you the results you specified, obviously though, this requires both a name and an address search term for a search to return any results. You didn't specify what happens if someone just searches for 'John', if you will always get both a name and address the above will work fine I think.

Answer 2

I think the computed column is your best option. It'll be the most flexible, given that you don't know which tokens will be in the search query, it'll perform better, and your stored procedure will be smaller.

In order to create a computed column based on data in another table, you will have to create it using a UDF (user defined function) like this:

CREATE FUNCTION dbo.udf_ComputedColumnFunction (
    @EmployeeId INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @RET VARCHAR(1000)

    SELECT
        @RET = e.FirstName + ' ' + e.LastName + ' ' + a.Street  + ' ' +  a.StreetNumber + ' ' + c.Name + ' ' + c.ZipCode
    FROM Employee e
    INNER JOIN Address a ON a.EmployeeId = e.EmployeeId
    INNER JOIN City c ON c.CityId = a.CityId

    RETURN @RET
END
GO


ALTER TABLE Employee
ADD SearchColumn AS dbo.udf_ComputedColumnFunction(EmployeeId)

If you don't want to do that, you could:

• Create an indexed view and add the FullText index onto that.
• Create a lookup table populated by a trigger or by periodically running a stored procedure.

Answer 3

I think you should create and index view and should join all the columns which can be used in FullText to be combined in one single column by separating them with spaces or dashes, as both are noise words for sql server 2005. Then on that indexed view create a full text index.

Contains table does not by default applies FormsOf Inflectional or Forms of Thesaurus. These two are good options to configure and use.

If you want to go only for "OR" then use FreeTextTable as if by default applies both Forms of Thesaurus and FormsOf inflectional.