Post reply

multi-table multi-column index possible?

  • April 30, 2004 at 2:04 pm

    #101990

    I am interested in doing a text search but the problem is this text spans over mutiple columns in multiple tables. So what's the best way to support this search in SQLServer? Is it possible to create a multi-table multi-column index? Any help will be appreciated.

     

  • April 30, 2004 at 2:31 pm

    #504887

    No. But you can try creating indexes views.

    Create the view that matches all the tables you need with schemabinding, and then create indexes on that view.

  • April 30, 2004 at 3:01 pm

    #504901

    Thanks! Any ideas as to whether the same solution can be used in other databases like Oracle, MySQL, DB2?

  • April 30, 2004 at 3:09 pm

    #504905

    Sorry, can't help you there.

  • April 30, 2004 at 3:32 pm

    #504911

    Going one step further, any ideas as to how a text search can be done in SQLServer? For example, I have two tables:

    t1 (n1 numeric, col1 varchar)

    t2 (n2 numeric, col2 varchar)

    insert into t1 (1,  'Name is John Doe')

    insert into t2 (5, 'The birthmonth is November')

    Now, my application wants to return these data rows if the user searches on either "John" or "November" or "Doe". So, basically I want to search for a string on different columns of two different tables. This string may be at the beginning, middle or at the end in the string value. Any clues?

    Thanks in advance for any help.

  • May 3, 2004 at 10:32 pm

    #505068

    Indexed Views are equivalent to Materialised Views in Oracle.

    There is no equivalent in MySQL. 

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply