Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Normalizing-Denormalized Tables Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 7:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 35,216, Visits: 31,670
Mohit (11/12/2008)
Toni Thank-you for the suggestion :).

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 :D.

- Mohit.


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


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #659001
Posted Friday, February 20, 2009 9:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Hi Jeff :),

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 :P.

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.


How to ask for help .. Read Best Practices here.
Post #661408
Posted Wednesday, August 26, 2009 12:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
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]
Post #777286
Posted Wednesday, August 26, 2009 12:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
the repeats of the posts have been accidentally submitted due to an application error with sqlservercentral.com
Post #777290
Posted Wednesday, August 26, 2009 12:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
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/
Post #777296
Posted Wednesday, August 26, 2009 12:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
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]
Post #777299
Posted Wednesday, August 26, 2009 12:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
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]
Post #777302
Posted Wednesday, August 26, 2009 12:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
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/
Post #777304
Posted Friday, November 1, 2013 1:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:38 AM
Points: 34, Visits: 161
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.
Post #1510513
Posted Friday, November 1, 2013 4:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 26, Visits: 188
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.



Post #1510545
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse