Did you say denormalize, why?

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

    John Esraelo

  • 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.'


  • 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.

    John Esraelo

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

  • 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!

    John Esraelo

  • 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
  • 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.

    John Esraelo

  • Thanks for the info.

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

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