CODEWITHSUNDEEP
sql-serversql-server-2005
EJ Beauchamp
EJ Beauchamp

Reputation: 63

Indexed view forcing non-optimal column type

We are trying to speed up some of our stored procs by reducing implicit conversions. One of the issues we are trying to figure out is how to fix several indexed views similar to this:

Time.UserID is INT
Time.TimeUnit is DECIMAL(9,2)
Time.BillingRate is MONEY

Select 
    UserID, 
    SUM(TimeUnit) as Hours, 
    SUM(TimeUnit*BillingRate) as BillableDollars
FROM 
    Time
GROUP BY 
    UserID

gives us a view with the columns:

UserID(int, null)  
Hours(decimal(38,2), null)  
BillableDollars(decimal(38,6), null)

We would prefer to have Hours(decimal(9,2),null) and BillableDollars(money,null).

CAST(SUM(TimeUnit*BillingRate) AS MONEY) as BillableDollars

Returned:

Cannot create the clustered index 'ix_indexName' on view 'x.dbo.vw_viewName' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

And we were worried about the efficiency of SUM(CAST(TimeUnit*BillingRate AS MONEY)) as BillableDollars

What would be the best way to preserve these column types or is there a 'best practice'?

Upvotes: 1

Views: 130

Answers (2)

wqw
wqw

Reputation: 11991

Just wrap your indexed view in second "casting" view like this

CREATE VIEW MyView
AS
SELECT  CAST(UserID AS INT)              AS UserID
        , CAST(TimeUnit AS DECIMAL(9,2)) AS TimeUnit
        , CAST(BillingRate AS MONEY)     AS BillingRate
FROM    MyViewIndexed WITH (NOEXPAND)

As a bonus you can include NOEXPAND hint so the underlying indexed view is actually utilized by query optimizer on less "advanced" editions of MSSQL.

Upvotes: 0

Rose
Rose

Reputation: 156

I might try adding a derived (actualized) "BillableDollars" column to the Time table with the conversion applied:

CONVERT(MONEY,(TimeUnit*BillingRate))

I've used money but, of course, the conversion could be to whatever data type most effectively meets your needs.

I believe this will allow you to have the indexed view while summing on the calculated billable dollars.

I expect that the reason you're getting a larger precision in the view datatype than the one in the table data type is that the sum of a bunch of numbers with a precision of 9 is adding up to a number that needs a precision greater than 9.

Upvotes: 2

Related Questions