Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Did you say denormalize, why?


Did you say denormalize, why?

Author
Message
John Esraelo-498130
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
Comments posted to this topic are about the item Did you say denormalize, why?

Cheers,
John Esraelo
Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 944
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.'


* [I may write an article about the EAV descent into madness]
John Esraelo-498130
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
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
SDB15
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 114
Couldn't you also accomplish the same thing using the PIVOT command? Wouldn't that perform better?
John Esraelo-498130
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
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
Anthony Scott
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 87
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
John Esraelo-498130
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1030
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
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9888 Visits: 885
Thanks for the info.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search