Did you say denormalize, why?

Question

Did you say denormalize, why?

John Esraelo-498130

SSCertifiable

Points: 5916
More actions
April 24, 2012 at 8:02 pm

#256964

Comments posted to this topic are about the item Did you say denormalize, why?
Cheers,
John Esraelo

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 1

We have code similar to this to manage EAV tables.*

I believe the dynamic SQL executing DDL in a loop reduces your database to a very expensive scripting environment for running procedural code.

I would propose moving this operation outside the database to either a reporting engine or a scripting/app tier. I can appreciate that this posted solution "works" - but I questions whether this kind of code should be allowed. I know, it's a single ad-hoc solution... but this technique can quickly become standard procedure. When there are a dozen developers writing ad-hoc solutions into production, you may be paying a high price for such 'convenience.'

*

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 2

You are absolutely right.

What I should have added as a comment or a warning is that .. "... this is for training purposes and should not be considered as a standard practice as it already violates several normalization norms and rules."

Of course as part of the title of this posting indicated that "WHY".

I leave the judgement to you folks as experts to allow / disallow and I do appreciate the comment actually.

Cheers,
John Esraelo

SDB15 SSC Eights! Points: 949 More actions · Answer 3

Couldn't you also accomplish the same thing using the PIVOT command? Wouldn't that perform better?

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 4

This is just a sample piece to demonstrate how to "denormalize" data.

Really a fun thing to play with and not taken seriously as a practical script for production.

As a matter of fact I am not fund of using "cursors" in large heavily used tables as will largely affect the performance.

What you are referring to is perhaps Matrix / cross-tab / pivot that always requires some sort of a quantifiable / statistical value in the middle which this script does not care about that.

But, by all means, please share with us if you find a PIVOT method that does denormalize 2 or more tables into 1.

This result may also be achieved in SSAS. But WHY? 😉

have fun!

Cheers,
John Esraelo

Anthony Scott SSC-Addicted Points: 408 More actions · Answer 5

John,

Thanks for the article, interesting read. We had a similar item here not long ago for a customized report, and I had the developer create a dynamic Pivot statement to achieve the results he needed using a string. This solution appears to produce similar results, but can be made more dynamic.

As you mentioned, I too detest the use of cursors and would change that to be a simple loop.

Whether or not this kind of script should be used in production is up to the developer (and their dba) but it provides great food for thought.

________________________________________________

We passed upon the stair - and I was that man who sold the world

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 6

Thank you for the comment and yes, you are right, it depends on the individual scenario / special cases / etc. and that traffic and the IO is not too bad and can be displaying the data in that fashion.

Cheers,
John Esraelo

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 1:55 pm

#1877969

Thanks for the info.