Computed columns best practices.

  • Hi all. I'm having a helluva time deciding whether or not to use a bunch of computed columns. I seem to be finding opposing arguments on both sides. Here's the gist of what I'm doing:
    Building out a fairly straight forward financial tracking system. There will be many charge accounts stored, and lists of charges against them. The users will need to see reports that slice and dice these numbers in a bunch of different ways. The system will NOT be high volume. There will only be a few users. There will be some robust reports generated.

    Given all that, it seems to me that persisted computed columns makes sense: limited amounts of new data creation, but heavier reporting where non-persisted columns columns would require constant recalculation in queries. But I'd like to hear from someone more seasoned in using them. Your thoughts?

    Thanks.

    Kurt

  • As always, "It Depends".  What will the computed columns be doing?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I'm with Jeff, first thought, it depends.
    However, I'll go a little farther. With low levels of data modification, even if the computed columns are doing a lot of work, the advantages of having persisted calculations will likely far outweigh the cost of those calculations. Testing it all though is the key. Don't trust what some yahoo like me says on the internet.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Jeff and Grant:

    Thanks for the replies, gentlemen. Initially the idea was to store calculations like decrements to charge accounts and their results, aggregates of all orders of types X, Y, Z, that sort of thing.

    But my team reminds me we are starting this project out on a new instance of SQL Server 2016 to take advantage of Always Encrypted, and this is the first expedition into AE territory for us, so now I'm thinking we may drop the persisted computations as I am just not savvy enough to know how that could affect things. We're under the gun to get this done and I feel it would be better to run tests using computed persisted columns + AE *before* we dive in. In other words, I'm thinking of taking the coward's route! 🙂

    Kurt

  • Kurt Wimberger - Tuesday, June 19, 2018 8:33 AM

    Jeff and Grant:

    Thanks for the replies, gentlemen. Initially the idea was to store calculations like decrements to charge accounts and their results, aggregates of all orders of types X, Y, Z, that sort of thing.

    But my team reminds me we are starting this project out on a new instance of SQL Server 2016 to take advantage of Always Encrypted, and this is the first expedition into AE territory for us, so now I'm thinking we may drop the persisted computations as I am just not savvy enough to know how that could affect things. We're under the gun to get this done and I feel it would be better to run tests using computed persisted columns + AE *before* we dive in. In other words, I'm thinking of taking the coward's route! 🙂

    Kurt

    I have zero experience with AE, so I may be completely off the mark.
    That said, my understanding is that AE encrypts the data before it gets to the DB.  If that is the case, then how will a computed column be able to compute anything, as it is unable to decrypt the data.

  • Des:

    Similar thought here. Not certain if it's possible, and if it is what kind of decrypt --> calculate --> encrypt cascade would each change cause? I would definitely want to test this all first.

    Kurt

  • DesNorton - Tuesday, June 19, 2018 10:55 AM

    I have zero experience with AE, so I may be completely off the mark.
    That said, my understanding is that AE encrypts the data before it gets to the DB.  If that is the case, then how will a computed column be able to compute anything, as it is unable to decrypt the data.

    The data is encrypted at rest, yes. That doesn't mean that normal processes are interrupted. A calculated column stores the data. It'll be encrypted there as well. However, the calculation would have taken place prior to the encryption.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks Grant. Very much appreciate the time you all devote to helping others.

    Kurt

  • Kurt Wimberger - Wednesday, June 20, 2018 7:18 AM

    Thanks Grant. Very much appreciate the time you all devote to helping others.

    Kurt

    Thanks... although reading what I said sounds slightly off. The calculation isn't done in a way that there is unencrypted data as you could read what I wrote. It's the internals that arrive at the calculations would be done normally. Data moving in/out of the engine will be encrypted and it'll be encrypted at rest. Inside the engine, well, that data is not encrypted. However, if someone is in your system so far that they can run the debugger on your SQL Server instance, I think you have other worries.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes, that's exactly what we have been saying at work as well! If the data in local memory is truly at risk then you have much, much larger problems. 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply