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 Monday, November 10, 2008 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:18 PM
Points: 6, Visits: 176
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?
Post #599795
Posted Monday, November 10, 2008 6:31 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, 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
Post #599815
Posted Monday, November 10, 2008 6:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2008 6:15 AM
Points: 26, 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.
Post #599833
Posted Monday, November 10, 2008 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
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
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
Post #599853
Posted Monday, November 10, 2008 7:36 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: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Thank-you everyone on the feed back :).

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.


How to ask for help .. Read Best Practices here.
Post #599870
Posted Monday, November 10, 2008 7:45 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 2:42 PM
Points: 33,278, Visits: 15,447
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
Post #599877
Posted Monday, November 10, 2008 8:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:33 PM
Points: 457, Visits: 476
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
Post #599904
Posted Monday, November 10, 2008 8:47 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
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. :P

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

(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 #599933
Posted Monday, November 10, 2008 8:50 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
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
Post #599935
Posted Monday, November 10, 2008 9:53 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
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! :)

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


Derek
Post #599981
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse