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 (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222676 Visits: 42003
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.
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: 3264 Visits: 1089
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.

---

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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222676 Visits: 42003
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.
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: 3264 Visits: 1089
I like using Red-Gate Data Generation for mass row generation Smile. 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 BigGrin.

- 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222676 Visits: 42003
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.
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
toniupstny
toniupstny
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 940
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


toniupstny
toniupstny
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 940
I removed a post relating to the Coalesce. Figured out I was being a dummy about it. Apologies.

Toni
Ed Klein-385786
Ed Klein-385786
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 478

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.
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: 3264 Visits: 1089
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.

---

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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222676 Visits: 42003
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.
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
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