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 12345»»»

Normalizing-Denormalized Tables Expand / Collapse
Author
Message
Posted Sunday, November 09, 2008 1:23 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: Monday, March 10, 2014 9:39 AM
Points: 942, Visits: 1,050
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.


How to ask for help .. Read Best Practices here.
Post #599528
Posted Sunday, November 09, 2008 12:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 12, Visits: 119
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
Post #599590
Posted Sunday, November 09, 2008 9:49 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #599635
Posted Sunday, November 09, 2008 11:30 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 942, Visits: 1,050
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 :).


---

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 #599653
Posted Sunday, November 09, 2008 11:34 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 942, Visits: 1,050
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.


How to ask for help .. Read Best Practices here.
Post #599654
Posted Monday, November 10, 2008 2:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:41 AM
Points: 55, Visits: 74
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.
Post #599692
Posted Monday, November 10, 2008 2:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 AM
Points: 1,317, Visits: 960
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



Post #599695
Posted Monday, November 10, 2008 4:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:21 PM
Points: 1,149, Visits: 871
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. :)
Post #599724
Posted Monday, November 10, 2008 4:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,198, Visits: 1,368
A good article to rectify the mess of creating unnormalized tables. After going thru this article people should realize the importance of normalization


Post #599741
Posted Monday, November 10, 2008 4:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #599743
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse