October 17, 2002 at 1:45 pm
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
October 17, 2002 at 6:17 pm
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
October 21, 2002 at 10:30 am
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.
October 21, 2002 at 12:07 pm
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....
October 21, 2002 at 3:18 pm
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
June 27, 2010 at 4:16 pm
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