locked
Computed Column Specification RRS feed

  • Question

  • Hi everyone, looking for some advice.

     1. Importing approx 50k records per day

     2. Each record has ProductUnitNetPrice

     3. I need to update the ProductUnitSalePrice by multiplying the ProductUnitNetPrice by 30%

    i.e. ProductUnitNetPrice £20.00 add 30% = £26.00 and update the ProductUnitSalePrice 

    I was thinking of using a Computed Column, just not sure how to do it.

    Any help or pointers much appreciated.






    • Edited by brucey54 Monday, February 11, 2019 8:03 PM
    Monday, February 11, 2019 8:00 PM

Answers

  • If you want to make it a computed column, you would say:

    ALTER TABLE tbl ADD ProductUnitSalePrice  AS ProductUnitNetPrice  * 1.3

    And indeed that is faster than adding the column as a persistent column and update the value, at least as long as you don't make it a persistent column.

    However, as Tom points out, it sounds very unlikely that the factor would be 1.3 for every product from now until the end of time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by brucey54 Tuesday, February 12, 2019 7:35 AM
    Monday, February 11, 2019 10:38 PM

All replies

  • I would not recommend a computed column for this purpose.  

    I would have an actual column with the price you want.   I doubt you will always want to make it 30% markup.

    Monday, February 11, 2019 8:28 PM
    Answerer
  • Thanks Tom, would you recommend a store procedure to update the column, the column is null at the moment.  

    I need some way to update the column with the markup price. I could update the column with code, but I thought it would be faster if sql updated the column.

    Monday, February 11, 2019 10:33 PM
  • If you want to make it a computed column, you would say:

    ALTER TABLE tbl ADD ProductUnitSalePrice  AS ProductUnitNetPrice  * 1.3

    And indeed that is faster than adding the column as a persistent column and update the value, at least as long as you don't make it a persistent column.

    However, as Tom points out, it sounds very unlikely that the factor would be 1.3 for every product from now until the end of time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by brucey54 Tuesday, February 12, 2019 7:35 AM
    Monday, February 11, 2019 10:38 PM
  • Thanks Erland, 

    if the markup factor 1.3 doesn't change much would you recommend a computer column?


    Monday, February 11, 2019 10:51 PM
  • Hi brucey54,

    It really depends, if the markup factor 1.3 doesn't change, we can also have other alternatives like creating a view or stored procedure.

    How often is data inserted into the table? How often is data retrieved from the table? These are all facts that we need to consider, if this table inserts a lot but select less, we can create a view for this table. If the table inserts less but select a lot, we can use the computer column.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 2:10 AM
  • if the markup factor 1.3 doesn't change much would you recommend a computer column?

    To answer that question, I would need to know a lot more about your system. It's not a good idea to do database design based only on fragmentary information. So I abstain answering that question.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, February 12, 2019 12:11 PM