SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Normalizing-Denormalized Tables


Normalizing-Denormalized Tables

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223144 Visits: 42003
Mohit (11/12/2008)
Toni Thank-you for the suggestion Smile.

I started working on the part-2 of this article, I will talk about all the suggestions in that article. If there are any more suggestions please feel free to make them. I am really greatful for all the help here BigGrin.

- Mohit.


Heh... you know I've just gotta ask, Mohit... Where's part 2? Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mohit K. Gupta
Mohit K. Gupta
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3272 Visits: 1089
Hi Jeff Smile,

It's on its way, I been busy with my research on the Theises for Masters. I have next two weeks off work; I plan to finish writing few articles for SQL Server and get more research in heh (at least thats the goal). I have most of the article done. I just got to fix up my English and proof read it to make sure I didn't make any stupid mistakes again Tongue.

Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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


How to ask for help .. Read Best Practices here.
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 162
here is a script i wrote that can normalise data from a single table into several tables with data and keys. it is useful when undertaking a normalisation process or when importing data into an existing database:
[url=http://www.sqlservercentral.com/articles/Normalization/64428/][/url]
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 162
the repeats of the posts have been accidentally submitted due to an application error with sqlservercentral.com
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 162
here is a script i wrote that helps normalise a single table into a number of tables. useful for normalising a database during the development phase.

http://www.sqlservercentral.com/scripts/Normalisation/66370/
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 162
here is a script i wrote that helps normalise a single table into a number of tables. useful for normalising a database during the development phase.

[url=http://www.sqlservercentral.com/scripts/Normalisation/66370/][/url]
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 162
here is a script i wrote that helps normalise a single table into a number of tables. useful for normalising a database during the development phase.

[url=http://www.sqlservercentral.com/scripts/Normalisation/66370/][/url]
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 162
here is a script i wrote that helps normalise a single table into a number of tables. useful for normalising a database during the development phase.

http://www.sqlservercentral.com/scripts/Normalisation/66370/
fregatepllada
fregatepllada
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 322
Well done,
I had a similar challenge during data migration, my solution was by splitting character-delimited values with XQUERY and applying transformation across a whole table.
deroby
deroby
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 316
Ed Klein-385786 (11/12/2008)

I'm just saying it would be nice. Do you have to go to that much effort to sum a numeric field? Why shouldn't accumulating text be as easy.


It's far from impossible, but the main issue is that you can't quite predict results as you're used from working with numbers.
Assume you'd be aggregating 3 records. The SUM would do 1 + 2 + 3 and the result would be 6. It doesn't really matter if internally it does 1 + 2 + 3 or 1 + 3 + 2 or 3 + 2 + 1 or etc...
However, if you're accumulating text, then it does become relevant in what order the records get processed. A + B + C = ABC is a different result from A + C + B = ACB, or C + B + A = CBA etc...

Given the way SQLCLR aggregations are coded it gets difficult quickly on how you want to implement this in order to get a consistent result; for things like COUNT(), SUM(), AVG(), MIN(), MAX() order has no influence and you can simply 'stream' through the input updating some working variables that then get returned in the end (after some minor processing). The amount of resources needed for this is rather low. But, if you want to have consistent results for the ACCTEXT() by e.g. sorting the values first, this means you have to bring them ALL in memory first, sort them along the way, then copy everything into one giant string and then return that again. And as if that's not bad enough already, if the Query Optimizer would decide to split the work over multiple threads this also includes merging the (presorted) data in the final step, sort it again and then do the conversion to one big string.

It's not un-doable, in fact I bet that if you google for it you're bound to find some implementations that are likely more worked out than what I just typed out of my head above. But from a "what's going on behind the scenes" point of view things could get ugly really fast I think.



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