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


Normalizing-Denormalized Tables


Normalizing-Denormalized Tables

Author
Message
Mohit K. Gupta
Mohit K. Gupta
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 1089
Comments posted to this topic are about the item Normalizing-Denormalized Tables

---

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.
MikeSpike
MikeSpike
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 148
The function CombineGroupNames() can be optimzed using this concat-logic:

DECLARE @Groups VarChar(MAX)

SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)
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

RETURN @Groups

/Mike
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114867 Visits: 41397
I'm all for normalizing data... but if no one is going to use the normalized form, then you shouldn't bother. In this case, it actually provides a negative ROI as well as a performance problem because the solution uses RBAR on steroids and the fact that no one has the nads to smack the vendor.

--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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 1089
Micke Schonning (11/9/2008)
The function CombineGroupNames() can be optimzed using this concat-logic:

DECLARE @Groups VarChar(MAX)

SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)
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

RETURN @Groups

/Mike


Thank Mike. I am still new to articles; I don't think I can make adjustment. But this is very helpful hint thanks Smile.

---

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.
Mohit K. Gupta
Mohit K. Gupta
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 1089
Hi Jeff,

Thank-you for the comments, I didn't think RBAR would be big issue in my case because the table had limited number of rows and wasn't going to grow. But I forgot to take into account when posting article here that other people reading this article it can be an issue. I am always telling the developers to stop working on the data in SQL Server on row-per-row basis; and I am doing it here myself. Thanks again for the *swift kick*; I will do better next time.

And your comment on Vendor, I have fought more then one battle against vendors. And lost them all, because I always get brought in at the last moment of when a system is going live; or when its been down for a while. So when working on it I find something that should be changed or altered. I get two responses, 1) it costs money to fix it and 2) we don't have time for it right now; we'll look at it later.

- 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.
daniel-851844
daniel-851844
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 96
I also wonder about the praticality of this. Surely it would only be important to normalize a 3rd parties table where the normalised tables would fully utilise CRUD (Create, read, update, & delete). Since the only "benefit" here is the read aspect I would think it would be better off untouched.
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2197 Visits: 1225
Hi Mihit,

Thank you for a nice example on how to get yourself away from bad table design, when retaining backwards compatibility.

What you could also have shown was the use of Insert Instead-of triggers, so that the view was updateable. Then you would have had 100% backwards compability.

I also wondered over your use of a cursor, but I thought that was because the set-based solution cannot garantee an ordered result.

Best regards,
Henrik Staun Poulsen
Denmark



Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1918 Visits: 1010
Nice article. There are some mistakes in examples which indicates lack of attention to details, but the intent for sharing ideas is most important.

You can expand on this. What you got is backward compatibility for select queries. Additionally, you can create "instead of" triggers on views, so you get update compatibility too. Idea for part II of the article. Smile
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8073 Visits: 1407
A good article to rectify the mess of creating unnormalized tables. After going thru this article people should realize the importance of normalization



StarNamer
StarNamer
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 1992
The function CombineGroupNames can be dispensed with entirely by defining the view thus:

CREATE View [dbo].[ApplicationResources]
AS
SELECT R.[ResourceName]
,R.[IsEnabled]
,replace(replace(replace( (
SELECT '['+sg.GroupName +']'
FROM ResourceSecurityGroup rsg
JOIN SecurityGroup sg ON rsg.GroupID=sg.GroupID
WHERE rsg.ResourceID=R.ResourceID
ORDER BY GroupName
FOR XML PATH('') ), '][', '|'), '[', ''), ']', '') as 'Groups'
FROM dbo.Resource R


Of course, with such as small dataset it's difficult to determine what's most efficient, but getting rid of the cursor is always a good idea.

[Edit] BTW, I agree with the comments that 'in the real world' it would probably not be worthwhile to do this on such a small dataset, but it does serve as a good example of a workaround to a problem where the vendor won't fix what may be an obvious problem. In fact, I've used similar workarounds (creating views for old tables) when a large application needs various tables restructuring but resource meant that all sections of the application couldn't be changed to use the new structure in one go.

Derek
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