[dba-SQLServer] Calculated Column Field in a table...

Asger Blond ab-mi at post3.tele.dk
Mon Jul 14 06:31:59 CDT 2008


Stuart and Darryl,

Calculated columns are calculated on the fly when querying the table. They
are NOT stored in the table unless you make an index on the calculated
column.
Non-indexed calculated columns are just a convenient way of having often
used calculations ready for table querying. If on the other hand you make an
index on a calculated column you actually persist the calculated results
which will speed up query performance compared to calculations in stored
procedures or views, but at the same time it will degrade performance of
insert and update operations against the table.

Asger


-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Stuart
McLachlan
Sendt: 14. juli 2008 08:36
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Calculated Column Field in a table...

Your last comment is right on the nail. You don't want to store calcuated
results in the table 
unless you have VERY good reasons to do so.

You can create either a View or a Stored Procedure to return Capex and Opex.

In a Stored Procedure it would be 

Select  
.....,
Case AccTreatmentId
	When 1 Then Dollars*AccountingSplit
	Else Dollars * (1-AccountingSplit)
End As Capex,
Case AccTreatmentId
	When 2 Then Dollars*AccountingSplit
	Else  Dollars * (1-AccountingSplit)
End As Opex,
.....


In the Design grid in a View, put

"CASE AccTreatmentId WHEN 1 THEN Dollars * AccountingSplit ELSE Dollars * (1
- 
AccountingSplit) END"  
as the Column value and "Capex" as the Alias and similarly for Opex.


Cheers,
Stuart


On 14 Jul 2008 at 15:16, Darryl Collins wrote:

> 
> Hi there,
> 
> I would have thought this was pretty simple, but I have a great ability to
complicate these things it seems - heh, early days for me SQL server.
> I have three fields in the table that I want to use for a calculated
result in a couple of other fields in the table.
> 
> "AccTreatmentID"
> 1: Opex
> 2: Capex
> 
> "AccountingSplit"
> 0%-100%
> 
> "Dollars"
> Manually entered dollar value
> 
> 
> Then I have two other fields (Capex and Opex) and I (think I) want to do
this.
> 
> "=iif([AccTreamtentID]=1,([Dollars]*[AccountingSplit]),0)" to get the Opex
Value
> "=iif([AccTreamtentID]=2,([Dollars]*[AccountingSplit]),0)" to get the
Capex Value
> 
> or If AccTreatment is Capex, then get the Capex Percentage and multiply by
the dollar value to get the capex amount.
> The actual formula is more complex as I need to determine the balance of
the percentage and then apply that to the remainder of the value (opex in
this case). anyway I am trying to keep it simple to start with.  (plus I
will probably need some sort of error test to return zero if part of the
data is missing - Am not sure if I need this or not in SQL server.)
> 
> The syntax in these formulae are not valid so I would like some help with
that.  And I guess my other question is do I actually need this at all?
> 
> Maybe I should do all the hack work in a query to report of Opex / Capex
splits.  Part of me suspect I dont need to store the calculated data at all
in the table at all and this is bad practise as it takes up time and space
for no real reason.
> 
> Anyone got any thoughts on this.
> 
> Cheers
> Darryll
> 
> This email and any attachments may contain privileged and confidential
information and are intended for the named addressee only. If you have
received this e-mail in error, please notify the sender and delete this
e-mail immediately. Any confidentiality, privilege or copyright is not
waived or lost because this e-mail has been sent to you in error. It is your
responsibility to check this e-mail and any attachments for viruses.  No
warranty is made that this material is free from computer virus or any other
defect or error.  Any loss/damage incurred by using this material is not the
sender's responsibility.  The sender's entire liability will be limited to
resupplying the material.
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



























































































































































































More information about the dba-SQLServer mailing list