# Normalizing-Denormalized Tables

• Mohit (11/10/2008)

Thanks Jeff. I will play with this later today; if I may can I reference this work in the followup article? I plan to use this and what I did to also show the performance differences. Thanks.

Absolutely no problem. Thanks for asking. How many rows do you intend to use to show the performance differences?

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

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Hi Jeff,

I was planning to create 5 databases, each with different number of records. One with the original 30, second with 300, 3rd with 1000, 4th with 10000 and last with 100000. I might do more if my computer can handle it, but I think it will demostrate the point very clearly with that.

Thanks.

[font="Arial"]---

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:[/font]

• Mohit (11/11/2008)

Hi Jeff,

I was planning to create 5 databases, each with different number of records. One with the original 30, second with 300, 3rd with 1000, 4th with 10000 and last with 100000. I might do more if my computer can handle it, but I think it will demostrate the point very clearly with that.

Thanks.

Cool... you got something to generate those rows?

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

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• I like using Red-Gate Data Generation for mass row generation :). I find it it works really well, and gives me the flexibility by using the regular expressions and such.

EDIT: When I first used red-gate tool, I was really impressed. I used the tool on a database that had 100+ tables. With FK constraints, check constraints, and field names with familiar keywords like first name, last name, phone number, and the tool was able to take all that into account to generate data that made sense with lists for names, and using regular expression for phone numbers to generate 10 digit numbers. I told it to generate 10,000 rows. So it generated 10000 rows x 100 tables = 1,000,000 rows of data keeping all rules valid in less then 5 min. Really quick and impressive I must say :D.

- Mohit.

[font="Arial"]---

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:[/font]

• Very cool... I didn't know about the product. Thanks.

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

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Hi. Nice article. I enjoy a challenge to remove RBAR. Since I went ahead and made it, I'll add mine to the non-cursor alternatives with no temp tables.

Toni

`SET QUOTED_IDENTIFIER ON`

`GO`

`SET ANSI_NULLS ON`

`GO`

`CREATE FUNCTION CombineGroupNames ( @ResourceID int )`

`RETURNS VarChar(2000)`

`AS`

`BEGIN`

`Declare @list varchar(2000) `

`set @list = ''`

`select @list = case @list when null then groupname else @list + groupname + '|' end`

` FROM Resource R`

` INNER JOIN ResourceSecurityGroup RG`

` ON R.ResourceID = RG.ResourceID`

` INNER JOIN SecurityGroup G`

` ON G.GroupID = RG.GroupID`

` WHERE R.ResourceID = @ResourceID`

`if Len(rtrim(@list)) > 0`

`begin`

`if charindex('|',@list,Len(@list)-1) > 0`

`select @list = Substring(@list,0,len(@list))`

`end`

`return @list`

`End`

`GO`

`SET QUOTED_IDENTIFIER OFF`

`GO`

`SET ANSI_NULLS ON`

`GO`

• I removed a post relating to the Coalesce. Figured out I was being a dummy about it. Apologies.

Toni

• Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?

--Jeff Moden

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.

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

[font="Arial"]---

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:[/font]

• Ed Klein (11/12/2008)

Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?

--Jeff Moden

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.

Got it... thanks, Ed.

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

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

[font="Arial"]---

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:[/font]

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

• the repeats of the posts have been accidentally submitted due to an application error with sqlservercentral.com

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

Viewing 15 posts - 31 through 45 (of 62 total)