SQL Server Full Text Search - Creating a Single Computed Column

Question

I am currently working on a project where I want to search for employees through only one input search query. For this I use SQL FTS.

The table layout looks like this:

Employee table

EmployeeId, Firstname, Lastname

Data examples

1, John, Miller

2, Chuck, Norris

---

Address table

AddressId, EmployeeId, CityId, Street, StreetNumber

Data examples

1, 1, 1, Avenue, 12

2, 2, 2, Wimbledon Rd, 12

---

City table

CityId, Name, ZipCode

Data examples

1, Hamburg, 22335

2, London, 12345

---

So now I have received the following search query:

• John Hamburg: means John And Hamburg and must return 1 record.
• John London: means John and London and should return 0 entries since there is no John in London.
• Norris Wimbledon: means Norris and Wimbledon and must return 1 record.

Now the problem is that using CONTAINSTABLE only allows you to search one table at a time. Thus, the use of "John AND Hamburg" in Employee The full text catalog returns 0 records, since "Hamburg" is in the address table.

Therefore, at present, I can only use “OR” instead of “AND,” for example:

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 leads to the fact that not only John, who lives in Hamburg, but every person named John and every person who lives in Hamburg returns. One solution I could think of is to somehow compute a column in the Employee table that contains all the necessary values ​​for a full-text search.

Employee table

EmployeeId, Firstname, Lastname, FulltextColumn

Data examples

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

So 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 it possible? Any other ideas?

Answer 1

you can use the connection to require matching both by name and by name of person.

 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, will give you the results that you indicated, obviously, however, this requires both a name and address search on demand to search for any results. You did not indicate what will happen if someone just searches for "John", if you always get the name and address, the above will work fine, I think.

Answer 2

I think the computed column is your best option. This will be the most flexible, given that you do not know what tokens will be in the search query, it will work better, and your stored procedure will be less.

To create a calculated column based on data in another table, you will need to create it using UDF (user-defined function), for example:

 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 do not want to do this, you can:

• Create an indexed view and add the FullText index to it.
• Create a lookup table populated with a trigger, or periodically execute a stored procedure.

Answer 3

I think you should create and index the view and join all the columns that you can use in FullText to combine them into a single column, separating them with spaces or dashes, since both are noise words for SQL Server 2005. Then this indexed view creates a complete text index.

Contains a non-default table; FormsOf Inflectional or Forms of the theurus are used. These two are good options to configure and use.

If you want to go only for “OR,” use FreeTextTable, as if by default both the thesaurus form and the FormsOf formal form were used.