InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 22:55:13 GMT20http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxhere 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/Wed, 26 Aug 2009 00:41:18 GMTkodracon http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxhere 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.[url=http://www.sqlservercentral.com/scripts/Normalisation/66370/][/url]Wed, 26 Aug 2009 00:38:20 GMTkodracon http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxhere 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.[url=http://www.sqlservercentral.com/scripts/Normalisation/66370/][/url]Wed, 26 Aug 2009 00:31:18 GMTkodracon http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxhere 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/Wed, 26 Aug 2009 00:27:05 GMTkodracon http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxthe repeats of the posts have been accidentally submitted due to an application error with sqlservercentral.comWed, 26 Aug 2009 00:20:05 GMTkodracon http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxhere 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=http://www.sqlservercentral.com/articles/Normalization/64428/][/url]Wed, 26 Aug 2009 00:13:07 GMTkodracon http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxHi 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.Fri, 20 Feb 2009 09:11:53 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Mohit (11/12/2008)[/b][hr]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.[/quote]Heh... you know I've just gotta ask, Mohit... Where's part 2? ;)Tue, 17 Feb 2009 19:00:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Ed Klein (11/12/2008)[/b][hr][quote]Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?--Jeff Moden[/quote]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.[/quote]Got it... thanks, Ed.Wed, 12 Nov 2008 17:33:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxToni 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.Wed, 12 Nov 2008 16:58:26 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote]Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?--Jeff Moden[/quote]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.Wed, 12 Nov 2008 08:17:35 GMTEd Klein-385786http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI removed a post relating to the Coalesce. Figured out I was being a dummy about it. Apologies.ToniWed, 12 Nov 2008 08:08:29 GMTtoniupstnyhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxHi. 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[code]SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE FUNCTION CombineGroupNames ( @ResourceID int )RETURNS VarChar(2000)ASBEGINDeclare @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 = @ResourceIDif Len(rtrim(@list)) > 0 begin if charindex('|',@list,Len(@list)-1) > 0 select @list = Substring(@list,0,len(@list)) endreturn @listEndGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO[/code]Wed, 12 Nov 2008 07:34:44 GMTtoniupstnyhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxVery cool... I didn't know about the product. Thanks.Wed, 12 Nov 2008 06:11:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI 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.Wed, 12 Nov 2008 05:59:38 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Mohit (11/11/2008)[/b][hr]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.[/quote]Cool... you got something to generate those rows?Tue, 11 Nov 2008 23:48:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxHi 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.Tue, 11 Nov 2008 22:04:36 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Mohit (11/10/2008)[/b][hr]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.[/quote]Absolutely no problem. Thanks for asking. How many rows do you intend to use to show the performance differences?Tue, 11 Nov 2008 20:51:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Ed Klein (11/11/2008)[/b][hr]This is a little off the immediate article and a little theoretical, but it applies to the issue of RBAR in the aggregating function. Why doesn't SQL provide a simple text aggregation function that works with text in a GROUP BY the same way SUM accumulates numbers. A function TextAccum(var,sep) where var is a text column and sep is a seperator to be inserted before all but the first value would eliminate the need for the CombineGroupNames function. If it overflows varchar(max), treat it the same as a numeric overflow. The whole view then becomes [code]SELECT R.[ResourceName] ,R.[IsEnabled] ,TextAccum(R.ResourceID,'|') AS Groups 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 GROUP BY R.[ResourceName] ,R.[IsEnabled][/code]No need for function definitions, or RBAR discussions. Why write a single use function each time you need to accumulate text. I know I'm not a sql guru, but this seems so obvious a need, I guess I'm looking for someone to tell me what I'm missing ???[/quote]Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?Tue, 11 Nov 2008 20:48:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]JJ B (11/10/2008)[/b][hr]Jeff, Thanks for doing all that work and also for the copy tip. Both came in helpful for me!- JJ[/quote]Glad it helped. Thanks for the feedback, JJ.Tue, 11 Nov 2008 20:45:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Derek Dongray (11/10/2008)[/b][hr]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?[/quote]Some say so... to be honest, I haven't tested it. I normally don't mix the two methods of joining.Tue, 11 Nov 2008 20:44:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]cs_troyk (11/11/2008)[/b][hr]Regardless of the practicality of the particular example used in this article, it is a good introduction to many of the considerations involved in changing a db design and maintaining backward-compatibility.One question I had was whether or not the GroupName and ResourceName columns should be allowed to contain duplicates within their respective tables. If not, it would be nice to see the uniqueness constraints declared for them.TroyK[/quote]Thank-you,I'll make sure to add that in the follow up article :).- Mohit.Tue, 11 Nov 2008 11:30:50 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxHeh you are not the only one in that thought I believe :).I just found this on Microsoft Connect:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125819&wa=wsignin1.0But I think because something similar is possible to do by means of XPath as listed in the article above; or by the examples given by many other reviewers (thanks!!!).- Mohit.Tue, 11 Nov 2008 11:30:09 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxThis is a little off the immediate article and a little theoretical, but it applies to the issue of RBAR in the aggregating function. Why doesn't SQL provide a simple text aggregation function that works with text in a GROUP BY the same way SUM accumulates numbers. A function TextAccum(var,sep) where var is a text column and sep is a seperator to be inserted before all but the first value would eliminate the need for the CombineGroupNames function. If it overflows varchar(max), treat it the same as a numeric overflow. The whole view then becomes [code]SELECT R.[ResourceName] ,R.[IsEnabled] ,TextAccum(R.ResourceID,'|') AS Groups 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 GROUP BY R.[ResourceName] ,R.[IsEnabled][/code]No need for function definitions, or RBAR discussions. Why write a single use function each time you need to accumulate text. I know I'm not a sql guru, but this seems so obvious a need, I guess I'm looking for someone to tell me what I'm missing ???Tue, 11 Nov 2008 10:50:41 GMTEd Klein-385786http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxRegardless of the practicality of the particular example used in this article, it is a good introduction to many of the considerations involved in changing a db design and maintaining backward-compatibility.One question I had was whether or not the GroupName and ResourceName columns should be allowed to contain duplicates within their respective tables. If not, it would be nice to see the uniqueness constraints declared for them.TroyKTue, 11 Nov 2008 09:06:28 GMTcs_troykhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI also have had to deal with bad design from 3rd party vendor. I made changes to supplied stored procedures to improve performanace / remove bugs. But there was one problem - every-time there was an upgrade the changes were lost.I tried to get them to look at their own code - but was unsuccessful.Tue, 11 Nov 2008 06:12:20 GMTsparky-407434http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Jeff Moden (11/10/2008)[/b][hr]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. :PIn 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". ....[/quote]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.Mon, 10 Nov 2008 10:09:36 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxJeff, Thanks for doing all that work and also for the copy tip. Both came in helpful for me!- JJMon, 10 Nov 2008 09:59:38 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxHi 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?Mon, 10 Nov 2008 09:53:26 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]WayneS (11/10/2008)[/b][hr]What I would recommend doing for it though is to instead of using all of the replace statements, is to:[/quote]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...[code]ALTER View [dbo].[ApplicationResources]ASSELECT 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 Rgo[/code]Mon, 10 Nov 2008 08:50:32 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspx[quote][b]Mohit (11/9/2008)[/b][hr]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.[/quote]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. :PIn 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...[code]--===== 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.;WITHcteSplitData 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, SecurityGroupIDGO--=======================================================================================-- 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 rGO--===== Sanity check the view SELECT * FROM dbo.ApplicationResources[/code]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.Mon, 10 Nov 2008 08:47:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI 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.Mon, 10 Nov 2008 08:17:39 GMTRon Kuncehttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI 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.Mon, 10 Nov 2008 07:45:51 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxThank-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.Mon, 10 Nov 2008 07:36:54 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI 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:[code]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[/code]Mon, 10 Nov 2008 07:20:04 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxI 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.Mon, 10 Nov 2008 06:51:52 GMTkayucahttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxThanks 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".Mon, 10 Nov 2008 06:31:09 GMTPaul DBhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxIf 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?Mon, 10 Nov 2008 06:04:50 GMTjwillis-839305http://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxThe function CombineGroupNames can be dispensed with entirely by defining the view thus:[code]CREATE View [dbo].[ApplicationResources]ASSELECT 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[/code]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.Mon, 10 Nov 2008 04:37:57 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic599528-1416-1.aspxA good article to rectify the mess of creating unnormalized tables. After going thru this article people should realize the importance of normalizationMon, 10 Nov 2008 04:37:22 GMTAnipaul