June 18, 2018 at 11:56 am
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
June 18, 2018 at 2:09 pm
As always, "It Depends". What will the computed columns be doing?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2018 at 2:33 am
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
June 19, 2018 at 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
June 19, 2018 at 10:55 am
Kurt Wimberger - Tuesday, June 19, 2018 8:33 AMJeff 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.
June 19, 2018 at 11:53 am
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
June 20, 2018 at 1:31 am
DesNorton - Tuesday, June 19, 2018 10:55 AMI 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
June 20, 2018 at 7:18 am
Thanks Grant. Very much appreciate the time you all devote to helping others.
Kurt
June 20, 2018 at 7:52 am
Kurt Wimberger - Wednesday, June 20, 2018 7:18 AMThanks 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
June 20, 2018 at 8:04 am
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