Indexed Views and GetDate()

  • Me again...

    Every month I need to run billing on my system. I use a set of logic to exclude rows that are non-billable AND I use this logic in multiple places. Thanks to other peoples insights - I'm trying to create an "Indexed View" so I can join to it whenever I need "Billable Accounts".

    But - Since I use a date as part of the criteria, I cannot create a useful UDF or add an Index to a view.

    Here's my current view(simplified)

    SELECT

    H.ID_Account,

    H.USDValueAmt

    FROM

    Account AC

    JOIN Details D

    ON AC.id_Account = D.ID_Account

    WHERE

    AC.BeginBillingDte <= GETDATE()

    AND AC.ID_VehicleType <> 7

    I don't want to edit the view every month and put in a new static date and I'm out of visible options.

    Any help is appreciated - B

  • Sorry not possible.

    Not sure what benefit you get if the billingdate and vehicletype are indexed.

    Also using <= today generally doesn't benefit from an index because it doesn;t filter the data much.

    Try creating the indexed view with aout the billingdate clause in the where clause

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • Sorry Simon, to clarify:

    I want to index the Account ID field for joins - not members of the where clause necerssarily. This way - I can join the accounts table to the "Billable Only" view on Account ID and be sure that all places I need "Billable" logic, the behaviour wil be the same.

    And - I NEED the BillingDate (which causes the Non-Determinism) since that is a big part of excluding Non-BIllable accounts. I simply can't remove it.

  • I think what Simon is suggesting is to create the indexed view without the date criteria, and then use the date criteria when calling the view, which would work, as the criteria would be passed through to the underlying query anyway. Would this not work for you?

    If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....

  • The indexed view cannot include the getdate() function as you have found. You have to create the indexed view without it and then a view with it.

    You could create a job that creates the indexed view based on the static date.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • This exact problem happens to be one of the questions on a 70-431 practice exam (SQL 2005) and according to the answer key the solution is: “Remove all non-deterministic function calls from within the view and schema-bind all functions that are called from within the view.”

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

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