Full Text Search Result Ranking Across Multiple but Similar Tables

Question

Hello,

I have a very large table that I had to break up into smaller tables and then full-text index it.

Example:

Very Large Table:                            T1

T1 broken into smaller tables:        S1, S2, S3 & S4 where the content of S1+S2+S3+S4=T1

S1-S4 tables all have the same columns i.e. Col1 and Col2.

I have individually created a unique index on Col1 of S1 through S4 tables.

I have individually full-text indexed Col2 of the S1 through S4 tables. 

To perform a full-text search query with ranking on the one of these tables I do the following: 

SELECT

                S1.*,

                KEY_TBL.RANK

FROM

                S1

                INNER JOIN  CONTAINSTABLE

                                (

                                                S1,

                                                *,

                                                N'"test"',

                                                500

                                ) AS KEY_TBL 

        ON S1.Col1 = KEY_TBL.[KEY]

Order by

                  KEY_TBL.RANK;

 

How can I extend the above query to perform the same search on S1 through S4 and rank the result across all the 4 tables?

I do realize that the ranking function in SQL Server (presumably Okapi BM25) logically makes sense if its performed within the context of a single table (not a series of tables). But then, perhaps, there could be an algorithm that performs a relative re-ordering of ranking results across multiple tables (all other variables being the same).

If this cumulative ranking query is not possible, is there a different path I can take (short of reverting back to the T1 table and indexing it).

Thanks,

Tareen

Answer 1

You can't expect to get meaningful results when you do something like this.

You might want to try to put an indexed view over all the tables. This will give you a consistent rank.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941