Darryl Collins
Darryl.Collins at coles.com.au
Mon Jul 14 19:27:57 CDT 2008
-----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Stuart McLachlan Sent: Tuesday, 15 July 2008 9:22 AM To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Calculated Column Field in a table... You're right. I don't use "computed columns" in tables generally and didn't think of doing so in this instance. When Darryl talked about "calculated columns", I was thinking in Access mode and assumed that he was intending to store the *results* of the calculation in ordinary data fields in the table. I really have to get over the mindset that tables are just data stores - in SQL Server, you can embed much of your business logic at the table level. Stuart On 14 Jul 2008 at 13:31, Asger Blond wrote: > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > 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 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.