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


Normalizing-Denormalized Tables


Normalizing-Denormalized Tables

Author
Message
jjwooty
jjwooty
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 233
If the tables were not being updated to often wouldn't an indexed view allow you to not have the processing of the view unless the the data changes?
Paul DB
Paul DB
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 258
Thanks for the well written, clear article. I like to see new authors give it a go. Consider updating this article to remove the RBAR, as suggested by the discussion. I hope to read your followup article soon, entitled "Normalizing-Denormalized tables Part II: Insert Instead-Of Triggers".

Paul DB
kayuca
kayuca
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 63
I want to set the record straight. I just read Mohit Gupta's piece on normalizing a not normalized table (Nov 10, '08). I accidently rated it with one star when I really wanted to rate it 5 stars. I hope I have not screwed up the average too badly. Kayuca.
WayneS
WayneS
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37420 Visits: 10755
I agree with using the SELECT ... FOR XML PATH('') to get a set based operation. Not only does it remove the cursor, but it greatly simplifies the code.
What I would recommend doing for it though is to instead of using all of the replace statements, is to:

declare @Groups varchar(max)
select @Groups = (
select '|' + 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
FOR XML PATH(''))

set @Groups = substring(@Groups, 2, 1024)
return @Groups



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Mohit K. Gupta
Mohit K. Gupta
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5390 Visits: 1089
Thank-you everyone on the feed back Smile.

I been working on the 2nd part of this article. Just been busy with studies for MCITP and my masters. But I plan to get that out soon; and I really appreciate the comments. I will address all the comments here.

When I did this solution we didn't have too many updates. We had a few updates where we had to add groups for some resources and remove it for others and our group name followed fully-quantified active-directory name (OU=Domain,OU=Dept,OU=Grp1|OU=Domain,OU=Dept,OU=Grp2|etc...) so if you miss a comma or pipe ("|") it caused issues. So even though I had to take a bit of a performance hit I figured it was worth the headache we kept running into. But I must admin I did not consider XPATH or String concatenation for the function as a solution. Thanks again for all the tips.

I didn't create index for the view to improve the peformance further, I can also look at doing that in part-2.

I'll try talking to SSC editors to see maybe I can revise my original article. So to eliminate the RBAR issues; and fix any other problems that have been identified.

Again, thanks a lot folks.

- 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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: Administrators
Points: 250156 Visits: 19813
I thought you had an interesting idea and it's worth considering for other people. I'm not sure how performance has changed. That would be an interesting follow-up.

Actually it would be good to show a follow-up article later that removes the RBAR and then compares the approaches. We can link the articles together.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ron Kunce
Ron Kunce
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1142 Visits: 496
I am very interested in follow-on article about the "Insert Instead of" trigger on the view. We are just beginning a massive project to normalize our databases while creating views to keep our legacy apps running. However, its this view trigger that will be the real key to our solution. Hope to see it soon.

Ron K.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)SSC Guru (381K reputation)

Group: General Forum Members
Points: 381839 Visits: 43006
Mohit (11/9/2008)
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.


Heh... Understood and appreciated... I've been there with non-compliant vendors myself. Same goes for "out sourcing"... lot's of them don't care about performance or RBAR because they can wash their hands of it once they have your money. I keep trying to invite them to one of my famous porkchop dinners, but I guess word has gotten out on that. Tongue

In SQL Server 2005, there are some tools to do this quite easily without any Cursors or While loops as you'll see in the code below. The view still has a bit of RBAR but it's not too bad... it doesn't use any REPLACE's... just one STUFF.

The entire "package" below is self standing including the creation of the original data. The whole thing runs in TempDB as a nice safe place to "play". Take a look...

--===== Do this in a safe place for testing
USE TempDB

--=======================================================================================
-- Make sure that none of the tables used in the demo below already exist.
-- This just ensures that everyone is on the same page for the demo.
-- Note that ALL these demo tables are in TempDB... a safe place for this demo.
--=======================================================================================
IF OBJECT_ID('TempDB.dbo.Tally','U') IS NOT NULL
DROP TABLE TempDB.dbo.Tally

IF OBJECT_ID('TempDB.dbo.OriginalTable','U') IS NOT NULL
DROP TABLE TempDB.dbo.OriginalTable

IF OBJECT_ID('TempDB.dbo.ResourceSecurityGroup','U') IS NOT NULL
DROP TABLE TempDB.dbo.ResourceSecurityGroup

IF OBJECT_ID('TempDB.dbo.Resource','U') IS NOT NULL
DROP TABLE TempDB.dbo.Resource

IF OBJECT_ID('TempDB.dbo.SecurityGroup','U') IS NOT NULL
DROP TABLE TempDB.dbo.SecurityGroup

IF OBJECT_ID('TempDB..#Work','U') IS NOT NULL
DROP TABLE #Work

IF OBJECT_ID('TempDB.dbo.ApplicationResources','V') IS NOT NULL
DROP VIEW dbo.ApplicationResources

--=======================================================================================
-- Create and populate a Tally table. This would normally be a permanent table and
-- NOT need to be regenerated everytime.
--=======================================================================================
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

--=======================================================================================
-- Setup the original denormalized table so we can demonstrate how to normalize it
-- using set based methods instead of Cursors and other RBAR.
--=======================================================================================
--===== Create a fasimile of the original table
CREATE TABLE OriginalTable
(
ResourceName VARCHAR(50),
IsEnabled CHAR(1),
Groups VARCHAR(1024),
CONSTRAINT PK_OriginalTable PRIMARY KEY CLUSTERED (ResourceName)
)

--===== Populate it with data from the article
INSERT INTO OriginalTable
(ResourceName, IsEnabled, Groups)
SELECT 'Resource1','Y','Grp1|Grp2|Grp3|Grp4' UNION ALL
SELECT 'Resource2','Y','Grp1|Grp3|Grp4' UNION ALL
SELECT 'Resource3','Y','Grp1|Grp2|Grp4' UNION ALL
SELECT 'Resource4','Y','Grp1|Grp2|Grp3' UNION ALL
SELECT 'Resource5','Y','Grp2|Grp3' UNION ALL
SELECT 'Resource6','Y','Grp1|Grp4'

--=======================================================================================
-- Create the new tables as was done in the article including FK constraints
--=======================================================================================
--===== New Resource table to contain only Resource information
CREATE TABLE dbo.Resource
(
ResourceID INT IDENTITY(1,1) NOT NULL,
ResourceName VARCHAR(50) NOT NULL,
IsEnabled CHAR(1) NOT NULL CONSTRAINT DF_Resource_IsEnabled DEFAULT ('Y'),
CONSTRAINT PK_Resource PRIMARY KEY CLUSTERED (ResourceID ASC)
)

--===== New SecurityGroup table to contain only Group information
CREATE TABLE dbo.SecurityGroup
(
SecurityGroupID INT IDENTITY(1,1) NOT NULL,
GroupName VARCHAR(255) NOT NULL,
CONSTRAINT PK_SecurityGroup PRIMARY KEY CLUSTERED (SecurityGroupID ASC)
)

--===== New ResourceSecurityGroup table provides the link between Resources and Groups
CREATE TABLE dbo.ResourceSecurityGroup
(
ResourceID INT NOT NULL,
SecurityGroupID INT NOT NULL,
CONSTRAINT PK_ResourceSecurityGroup PRIMARY KEY CLUSTERED
(ResourceID ASC, SecurityGroupID ASC)
)

--===== Create the required FK's
ALTER TABLE dbo.ResourceSecurityGroup WITH CHECK
ADD CONSTRAINT FK_ResourceSecurityGroup_Resource_ResourceID
FOREIGN KEY (ResourceID)
REFERENCES dbo.Resource (ResourceID)

ALTER TABLE dbo.ResourceSecurityGroup WITH CHECK
ADD CONSTRAINT FK_ResourceSecurityGroup_SecurityGroup_SecurityGroupID
FOREIGN KEY (SecurityGroupID)
REFERENCES dbo.SecurityGroup (SecurityGroupID)

--=======================================================================================
-- Populate the new tables without the use of RBAR
--=======================================================================================
--===== Split the groups and assign ResourceID's and SecurityGroupID's for all rows.
-- Data is stored in a temp table and all relationships are established by this
-- single non-RBAR step. Even the cross-table links are established for population
-- of the ResourceSecurityGroup table without any RBAR.
;WITH
cteSplitData AS
(
SELECT ResourceName,
IsEnabled,
SUBSTRING('|'+orig.Groups, t.N+1, CHARINDEX('|', orig.Groups+'|', t.N)-t.N) AS GroupName
FROM dbo.Tally t
RIGHT OUTER JOIN --Necessary in case orig.Groups is NULL
dbo.OriginalTable orig
ON SUBSTRING('|'+orig.Groups, t.N, 1) = '|'
AND t.N < LEN('|'+orig.Groups)
)
SELECT DENSE_RANK() OVER (ORDER BY ResourceName) AS ResourceID,
ResourceName,
IsEnabled,
DENSE_RANK() OVER (ORDER BY GroupName) AS SecurityGroupID,
GroupName
INTO #Work
FROM cteSplitData

--===== Populate the Resource table with normalized data
SET IDENTITY_INSERT dbo.Resource ON
INSERT INTO dbo.Resource
(ResourceID, ResourceName, IsEnabled)
SELECT DISTINCT
ResourceID, ResourceName, IsEnabled
FROM #Work
ORDER BY ResourceID
SET IDENTITY_INSERT dbo.Resource OFF

--===== Populate the SecurityGroup table with normalized data
SET IDENTITY_INSERT dbo.SecurityGroup ON
INSERT INTO dbo.SecurityGroup
(SecurityGroupID, GroupName)
SELECT DISTINCT
SecurityGroupID, GroupName
FROM #Work
ORDER BY SecurityGroupID
SET IDENTITY_INSERT dbo.SecurityGroup OFF

--===== Populate the ResourceSecurityGroup link table with normalized data
INSERT INTO dbo.ResourceSecurityGroup
(ResourceID, SecurityGroupID)
SELECT DISTINCT
ResourceID, SecurityGroupID
FROM #Work
ORDER BY ResourceID, SecurityGroupID

--=======================================================================================
-- As a sanity check, display the contents of the work table and the 3 new tables
--=======================================================================================
SELECT * FROM #Work ORDER BY ResourceID, SecurityGroupID
SELECT * FROM dbo.Resource ORDER BY ResourceID
SELECT * FROM dbo.SecurityGroup ORDER BY SecurityGroupID
SELECT * FROM dbo.ResourceSecurityGroup ORDER BY ResourceID, SecurityGroupID
GO
--=======================================================================================
-- Create the view that puts this all back together for the existing app.
--=======================================================================================
CREATE VIEW dbo.ApplicationResources
AS
SELECT r.ResourceName,
r.IsEnabled,
STUFF((SELECT '|'+ sg.GroupName
FROM dbo.ResourceSecurityGroup rsg
INNER JOIN SecurityGroup sg
ON rsg.SecurityGroupID = sg.SecurityGroupID
AND rsg.ResourceID = r.ResourceID
ORDER BY sg.GroupName
FOR XML PATH('')),1,1,'') AS 'Groups'
FROM dbo.Resource r
GO
--===== Sanity check the view
SELECT * FROM dbo.ApplicationResources



Don't forget... if you want to copy the code as formatted above, click and hold on the line just above the code box and drag to the line just below the code box. Then, copy and paste and the code should come in nicely formatted.

--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
StarNamer
StarNamer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3962 Visits: 1992
WayneS (11/10/2008)
What I would recommend doing for it though is to instead of using all of the replace statements, is to:
Yes. The REPLACEs were used because the case where I lifted the code from actually needed to put a prefix on as well as generating the separator, i.e. replace(replace(replace(...,'][',','),'[],'Entries are: '),']',''). You can still dispence with the UDF...
ALTER View [dbo].[ApplicationResources]
AS
SELECT R.[ResourceName]
,R.[IsEnabled]
,substring((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('')),2,1000) as 'Groups'
FROM dbo.Resource R
go



Derek
StarNamer
StarNamer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3962 Visits: 1992
Hi Jeef,

I just learned a function ... STUFF ... which solves the problem I was aware of relating to value chosen for the 3rd argument to SUBSTRING ... There isn't one! Smile

Also, is merging the subquery WHERE clause into an AND on the JOIN merely personal preference or is it more efficient?

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