November 3, 2013 at 4:41 pm
John Rees (11/3/2013)
Intriguing! I can understand how overflow could easily occur, but do you have examples of how precision of exact numerics (particularly decimal) can be affected by order of aggregation?
Interesting. You've made me rethink. As far as I can see the rounding error won't be changed by order for exact numerics unless the numbers involved don't all have the same scale and precision; I don't think that that can arise in the context of an aggregate. I was told aggregates of exact numerics had this problem way back when by someone regarded by me and by colleagues as an expert, and never bothered to work out just how it would happen for aggregates because of course I knew that rounding errors for exact numerics could be affected by evaluation order when doing arithmetic step by step. In an aggregate every value has the same precision and scale, and I think addition can't cause rounding, only overflow; that means of course that there will be overflow in cases where one would prefer to accept a rounding error, but that's a very different question.
Tom
November 4, 2013 at 10:46 am
Hmm, sorry for late reply.
I didn't realize article got republished. My apologies, ever since I started working at Microsoft I have not had opportunity to follow SQL Server Central as closing as I would like.
When I wrote this article, I didn't take many things into consideration. However the primary reason for article was to provide normalization, which made it easier for administrative staff to manage the administrative tables from within the system. It does introduce challenges when upgrading, any changes introduced to a 3rd party vendor software, whether it is Microsoft or another system. Therefore my normal recommendation now days, I would provide in this scenario is, make sure you have a rollback script. Therefore if your change helps your system, keep it. However if a update is being released, you have to rollback your change in order to minimize the risk or impact on the upgrade. Another alternative I have seen people take is, if a change is significant enough they can reach out to their 3rd party vendor to suggest improvements. In all changes there is risk and issues with supportability of the product by your team and the support policy of third party vendor.
There are multiple improvements that can be made to original solution, however I never finished the version 2 of this; all the advice I had gotten from Jeff and gang here was going towards that article. But life had other plans for me, so never came back to the article. My apologies.
Thanks again for valuable feedback, SSC is great :).
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
November 11, 2013 at 3:05 am
Mohit K. Gupta (11/4/2013)
When I wrote this article, I didn't take many things into consideration. However the primary reason for article was to provide normalization, which made it easier for administrative staff to manage the administrative tables from within the system.
Yes, you got that right 😉 Thanks for the article, though, because it is a very nice example of exactly "normalization in the background". Newbies searching for inspiration only needs to disregard the use of views with user-defined functions - and cursors... That is the top three performance-killers, all in one go. Not a matter here, but definitely not scalable.
And then the article needed to finish by addressing the update/delete routine "by many people" which is mentioned in the beginning. The whole purpose of creating 5 objects to replace 1 could do with some emphasizing: That 3rd vendor application won't fail due to errors in the back-end maintenance, once a new front for maintenance has been written. The normalization described is mandatory for that to succeed.
Viewing 3 posts - 61 through 62 (of 62 total)
You must be logged in to reply to this topic. Login to reply