Using Full-Text Search in SQL Server 2008 across multiple tables, columns

Question

I need to search across multiple columns from two tables in my database using Full-Text Search. The two tables in question have the relevant columns full-text indexed.

The reason I'm opting for Full-text search: 1. To be able to search accented words easily (cafè) 2. To be able to rank according to word proximity, etc. 3. "Did you mean XXX?" functionality

Here is a dummy table structure, to illustrate the challenge:

Table Book
BookID
Name (Full-text indexed)
Notes (Full-text indexed)

Table Shelf
ShelfID
BookID

Table ShelfAuthor
AuthorID
ShelfID

Table Author
AuthorID
Name (Full-text indexed)

I need to search across Book Name, Book Notes and Author Name.

I know of two ways to accomplish this:

1. Using a Full-text Indexed View: This would have been my preferred method, but I can't do this because for a view to be full-text indexed, it needs to be schemabound, not have any outer joins, have a unique index. The view I will need to get my data does not satisfy these constraints (it contains many other joined tables I need to get data from).

2. Using joins in a stored procedure: The problem with this approach is that I need to have the results sorted by rank. If I am making multiple joins across the tables, SQL Server won't search across multiple fields by default. I can combine two individual CONTAINS queries on the two linked tables, but I don't know of a way to extract the combined rank from the two search queries. For example, if I search for 'Arthur', the results of both the Book query and the Author query should be taken into account and weighted accordingly.

Answer 1

Using FREETEXTTABLE, you just need to design some algorithm to calculate the merged rank on each joined table result. The example below skews the result towards hits from the book table.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

Note that I simplified your schema for this example.

Answer 2

I had the same problem as you but it actually involved 10 tables (a Users table and several others for information)

I did my first query using FREETEXT in the WHERE clause for each table but the query was taking far too long.

I then saw several replies about using FREETEXTTABLE instead and checking for not nulls values in the key column for each table, but that took also to long to execute.

I fixed it by using a combination of FREETEXTTABLE and UNION selects:

SELECT Users.* FROM Users INNER JOIN
(SELECT Users.UserId FROM Users INNER JOIN FREETEXTTABLE(Users, (column1, column2), @variableWithSearchTerm) UsersFT ON Users.UserId = UsersFT.key
UNION
SELECT Table1.UserId FROM Table1 INNER JOIN FREETEXTTABLE(Table1, TextColumn, @variableWithSearchTerm) Table1FT ON Table1.UserId = Table1FT.key
UNION
SELECT Table2.UserId FROM Table2 INNER JOIN FREETEXTTABLE(Table2, TextColumn, @variableWithSearchTerm) Table2FT ON Table2.UserId = Table2FT.key
... --same for all tables
) fts ON Users.UserId = fts.UserId

This proved to be incredibly much faster.

I hope it helps.

Answer 3

I don't think the accepted answer will solve the problem. If you try to find all the books from a certain author and, therefore, use the author's name (or part of it) as the search criteria, the only books returned by the query will be those which have the search criteria in its own name.

The only way I see around this problem is to replicate the Author's columns that you wish to search by in the Book table and index those columns (or column since it would probably be smart to store the author's relevant information in an XML column in the Book table).

Answer 4

FWIW, in a similar situation our DBA created DML triggers to maintain a dedicated full-text search table. It was not possible to use a materialized view because of its many restrictions.

Answer 5

I would use a stored procedure. The full text method or whatever returns a rank which you can sort by. I am not sure how they will be weighted against eachother, but I'm sure you could tinker for awhile and figure it out. For example:

Select SearchResults.key, SearchResults.rank From FREETEXTTABLE(myColumn, *, @searchString) as SearchResults Order By SearchResults.rank Desc

Answer 6

This answer is well overdue, but one way to do this if you cannot modify primary tables is to create a new table with the search parameters added to one column.

Then create a full text index on that column and query that column.

Example

SELECT 
    FT_TBL.[EANHotelID]                 AS HotelID, 
    ISNULL(FT_TBL.[Name],'-')           AS HotelName,
    ISNULL(FT_TBL.[Address1],'-')       AS HotelAddress,
    ISNULL(FT_TBL.[City],'-')           AS HotelCity,
    ISNULL(FT_TBL.[StateProvince],'-')  AS HotelCountyState,
    ISNULL(FT_TBL.[PostalCode],'-')     AS HotelPostZipCode,
    ISNULL(FT_TBL.[Latitude],0.00)      AS HotelLatitude,
    ISNULL(FT_TBL.[Longitude],0.00)     AS HotelLongitude,
    ISNULL(FT_TBL.[CheckInTime],'-')    AS HotelCheckinTime,
    ISNULL(FT_TBL.[CheckOutTime],'-')   AS HotelCheckOutTime,
    ISNULL(b.[CountryName],'-')         AS HotelCountry,
    ISNULL(c.PropertyDescription,'-')   AS HotelDescription,
    KEY_TBL.RANK 

    FROM [EAN].[dbo].[tblactivepropertylist] AS FT_TBL INNER JOIN
     CONTAINSTABLE ([EAN].[dbo].[tblEanFullTextSearch], FullTextSearchColumn, @s)
      AS KEY_TBL
    ON FT_TBL.EANHotelID = KEY_TBL.[KEY]
    INNER JOIN [EAN].[dbo].[tblCountrylist] b
    ON FT_TBL.Country = b.CountryCode
    INNER JOIN [EAN].[dbo].[tblPropertyDescriptionList] c
    ON FT_TBL.[EANHotelID] = c.EANHotelID

In the code above [EAN].[dbo].[tblEanFullTextSearch], FullTextSearchColumn is the new table and column with the fields added, you can now do a query on the new table with joins to the table you want to display the data from.

Hope this helps