Normalizing-Denormalized Tables

Question

Normalizing-Denormalized Tables

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

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

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.

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

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

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

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 3

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.

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 4

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

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

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 5

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.

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 6

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 Hall of Fame Points: 3986 More actions · Answer 7

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

Toni

Ed Klein-385786 Ten Centuries Points: 1213 More actions · Answer 8

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 SSChampion Points: 12130 More actions · Answer 9

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

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

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 10

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.

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 11

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.

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 12

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

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

How to ask for help .. Read Best Practices here[/url].

kodracon Ten Centuries Points: 1027 More actions · Answer 13

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]

kodracon Ten Centuries Points: 1027 More actions · Answer 14

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

kodracon Ten Centuries Points: 1027 More actions · Answer 15

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/