Full Text Index on Table Versus View


  • Hello all,

    we know that FTI can be created at a table or view level as long as prerequisites are satisfified.

    I have a requirement to implement FTS on multiple identical tables(table parttioning) for a given FTS query.

    I can think of three possible solutions as of now.

    1.Create FTI on each individual table and then write FTS query against each table with union all to get the final results.

    2.Create FTI on a single view which selects data from all tables using Union all.

    3.Create FTI against each view created one for each base table and then issue FTS query against all views with Union all. 

    I came to know that there are lot of restrictions on view definition while creating FTI on it like schema binding, schema.table name, restriction of few SQl caluses like table hints, Union All, Min,MAx,Top,Ouer join queries.

    So i am thinking solution 2 may not be possible.

    Can somebody suggest when to create FTI on table vs view and pros and cons?

    Can somebody suggest any better approach for this kind of scenario?



    12 Maret 2012 11:59

Semua Balasan