Post reply

ALTER VIEW-will index be recreated or reindexed?

  • November 21, 2006 at 11:50 am

    #75098

    Once a month we need to recreate a view for reporting purposes. The tables in the join are growing and the number of rows in the view is ~5 million at the moment. The view needs to be alter each month because we are q

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • November 21, 2006 at 11:53 am

    #673281

    Something went wrong. Can you repost the message?

  • November 21, 2006 at 11:54 am

    #673282

    SORRY - Fat fingered it

    the rest is:

    we are querying vendor tables that are created each month with a common prefix but a random suffix.

    Since we can't change the vendor tables, we are thinking of creating an indexed view which would use UNION on the monthly tables.

    The question is, will an ALTER VIEW cause a reindex or will it drop and create the index?

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • November 21, 2006 at 11:58 am

    #673285

    No idea... and no time to create a test for it.  I'm anxious to see how this one turns out however .

  • November 22, 2006 at 7:18 am

    #673448

    Looks like we won't be able to use an indexed view. M$ says it can't have a UNION and requires a unique clustered index. We can't meet either requirement and since the indexed view would have almost the same overhead as a table, it looks like we'll be loading into a table every month.

    Would have been interesting, though. I don't have the time to create a test for it either Maybe after the holiday season.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

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

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