InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:05:47 GMT20http://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bob Hovious (2/27/2009)[/b][hr]I was referring to inadequacies of alleged IT professionals ;) My war story is that our development group used to have a guy whose entire job was to import data. I found this out after we went through some rightsizing and he was let go. When I reviewed his code it turned out to be slow AND inaccurate. He was driving it through a VB program with maximum RBAR. We just scrapped it all. Now things he used to run for a day are handled in under a minute, without duplications or omissions. No brilliance required, it was just a sad example of developers (who don't understand database) thinking that knowing how to do an INSERT or an UPDATE is all there is to it.[/quote]Looks like were migrating back in the direction of The Thread. :)My current job, the prior DBA was okay for some things, but for some aweful reason, he built a whole series of DTS packages that import text files into tables in SQL, and it's all done through VBS. Open file item, set a bunch of variables to the values in the first row, build an SQL string that does an insert statement with those variables, move to the next line in the file, rinse, repeat. Over 7-million times on one of the files. Some of these take as much as six hours to import a few hundred thousand lines of text. No transformations, just move from text to SQL, same columns and everything.He also wrote this wonderfully complex archive process for another database. The source database is third-party and subject to change without notice. The process queried the schema data for the tables, built a select and insert statement from sys.columns, dynamically, then dumped the data into a holding table using Select...Into..., then finally moved the data into the final archive tables, with the same structure as the primary tables. One problem was that it didn't compare the table structure and modify the target table, which completely defeated the purpose of the dynamic SQL. Errored out at least once because the target table didn't have a column that the source table did. The other problem was that it's a daily job, and the run-time was up to just over 20 hours, and the data is growing. So I changed it to a simple "Insert ... Select * from ... for XML raw, type" and an insert into a single XML column. No dynamic SQL, no staging tables, but handles schema changes without problem. Took the whole process down to about 3 minutes, and this copy is moving the data across a network to a different database server. Uses about 10% more disk space, and is somewhat more complex to query (XQuery instead of just straight XML), though views help with that, but worth it for not tying up a whole server for 20+ hours per day.On the other hand, he built a number of reasonably good databases, made sure there was adequate automatic maintenance on them, managed the backups and such, and otherwise did his job. Just those two things were pretty messed up.They also had a dev here who did a lot of "pay no attention to the man behind the curtain" type support for his applications. In other words, they don't run unless someone holds their hand and issues "back door" commands. Everyone thought he was great, till he left. His name was Dan, and now it's not uncommon to have people refer to broken things as "Dangineered" (pronounced like "engineered", but with his name incorporated).So, yeah, some time spent on such things. But for me, it's not even close to the majority of my time. Most of it is spent constructively, either on new systems or new features for existing systems.Mon, 02 Mar 2009 07:07:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bob Hovious (2/27/2009)[/b][hr]I was referring to inadequacies of alleged IT professionals ;) My war story is that our development group used to have a guy whose entire job was to import data. I found this out after we went through some rightsizing and he was let go. When I reviewed his code it turned out to be slow AND inaccurate. He was driving it through a VB program with maximum RBAR. We just scrapped it all. Now things he used to run for a day are handled in under a minute, without duplications or omissions. No brilliance required, it was just a sad example of developers (who don't understand database) thinking that knowing how to do an INSERT or an UPDATE is all there is to it.[/quote]The really sad part is, some people probably thought he was doing a good job.And, absolutely spot on... just because someone walked by a book store that sells SQL books, doesn't mean that they even know how to spell it. ;) I pretty much blame management for "bad" SQL Developers because it's [i]those [/i]managers that are totally clueless... For example... you've seen the "want ads"... [i]"Wanted, Web developer. Must have x number of years experience with Java/VB/C#/flavor-of-the-month and 1 year of experience with SQL."[/i] Such a person (usually, there are those wonderful exceptions) shouldn't even be allowed near the DB except by calling stored procedures that someone else wrote. But, sure enough, they get put into the position of solving ETL and other large batch problems.Fri, 27 Feb 2009 18:30:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bob Hovious (2/26/2009)[/b][hr][quote]files that have been mangled by people who don't have the faintest clue what they're doing[/quote]I just have to ask, how much of your coding efforts are spent on compensating for the inadequacies of others? [/quote]I make about 90-95% of my living at it. The other 5-10% is building new stuff and maintaining servers.Fri, 27 Feb 2009 18:22:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bruce W Cassidy (2/26/2009)[/b][hr][font="Verdana"]Any chance of you sharing some of the techniques you're using for that? I've been doing similar things, building an ODS for a client company. But I use bcp :DI have issues with SSIS. Namely, to use it well, you have to be fluent in not one, but [b]three[/b] programming languages at once (SQL for your database connections, VB for any plug-in code and C for the inline code). It's incredibly complex, and it's insanely hard to try and modify. I have met several companies who have SSIS packages that everyone flat out refuses to change simply because of the complexity of even just understanding what it does.Give me well-written straight line code in T-SQL any day. It may not perform quite as well as well-written SSIS, but at least I can easily understand and change it.[/font][/quote]Sure... most of it is just importing whole rows of the file into a 2 column table... one for an IDENTITY and one for the data. Of course, that requires the use of a BCP format file so I can skip over the identity.Then, I simply do a Tally table split to an NVP (Name/Value Pair) table in order by the IDENTITY column (which gets saved in the NVP) and the order of the Tally table.The reason I do this is two fold... that allows me to split the data into "columns" in the NVP without knowing how many there are and it also allows me to skip rows with BCP (Bulk Insert, actually). Neither like it very much when there are different numbers of delimiters in the header rows than the body of the file and they won't skip rows based just on the EOL character... the number of delimiters must match in all rows. So, I just treat each row as if it had no delimiters to allow the skip of 20 or 30 header rows.Note that I do NOT use a splitter function for this... I split the whole table all at once. If you haven't seen that before, take a look at the code in the following article (near the end)...[url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url]The section that I'm talking about in the article is titled, [font="Arial Black"]"One Final "Split" Trick with the Tally Table".[/font]Fri, 27 Feb 2009 18:19:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxI was referring to inadequacies of alleged IT professionals ;) My war story is that our development group used to have a guy whose entire job was to import data. I found this out after we went through some rightsizing and he was let go. When I reviewed his code it turned out to be slow AND inaccurate. He was driving it through a VB program with maximum RBAR. We just scrapped it all. Now things he used to run for a day are handled in under a minute, without duplications or omissions. No brilliance required, it was just a sad example of developers (who don't understand database) thinking that knowing how to do an INSERT or an UPDATE is all there is to it.Fri, 27 Feb 2009 08:17:25 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bob Hovious (2/26/2009)[/b][hr][quote]files that have been mangled by people who don't have the faintest clue what they're doing[/quote]I just have to ask, how much of your coding efforts are spent on compensating for the inadequacies of others? [/quote]Not much. For the most part, a customer not knowing how to format a spreadsheet for import into SQL isn't their problem. That's what I'm being paid for.Fri, 27 Feb 2009 08:01:31 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote]files that have been mangled by people who don't have the faintest clue what they're doing[/quote]I just have to ask, how much of your coding efforts are spent on compensating for the inadequacies of others? Thu, 26 Feb 2009 16:47:29 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxI've found SSIS is great for files with a relative degree of consistency, and has major problems with files that have been mangled by people who don't have the faintest clue what they're doing. What I've done in those cases is pretty much pull the whole thing into a table with 1 column of nvarchar(max) and an ID and nothing else, then process in T-SQL from there.Didn't have a prior automated process. Before I took it on, it was manual, and took about an hour per file. I took it down, eventually, to about 10 seconds per file (average file size about 5k rows, up to about 20k for the biggest ones). Numbers table and such were key to the process. I could do even better with 2k5 and some of the XML functions, but this was on 2k and didn't have what I would have used.Thu, 26 Feb 2009 13:13:16 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[font="Verdana"]Any chance of you sharing some of the techniques you're using for that? I've been doing similar things, building an ODS for a client company. But I use bcp :DI have issues with SSIS. Namely, to use it well, you have to be fluent in not one, but [b]three[/b] programming languages at once (SQL for your database connections, VB for any plug-in code and C for the inline code). It's incredibly complex, and it's insanely hard to try and modify. I have met several companies who have SSIS packages that everyone flat out refuses to change simply because of the complexity of even just understanding what it does.Give me well-written straight line code in T-SQL any day. It may not perform quite as well as well-written SSIS, but at least I can easily understand and change it.[/font]Thu, 26 Feb 2009 13:11:32 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bruce W Cassidy (2/26/2009)[/b][hr][font="Verdana"]One of the recent (i.e. in the last ten years) changes in data warehousing has been a move away from specialised ETL tools like Informatica, Data Stage, etc, and towards letting the RDBMS do the work. This approach has been called ELT (extract, load, transform).For smaller data warehouses, this makes sense. There's no need to support two disctinct platforms for throwing data around. It's all done in the RDBMS.Oracle have a product that does that (sorry, can't remember the name), and we use a product at work named Wherescape RED that does the same over the top of SQL Server 2005.So the database does a [b]lot[/b] of work in chopping, changing and reformatting data during the staging process. There's no real presentation layer, as the data just gets posted into another database.That's a [b]very[/b] valid reason to be looking at string operations within an RDBMS, from my perspective.[/font][/quote]Very cool... I've been doing it that way since day 1 with SQL Server... currently working on a project that I've dubbed as "STEPS".... "Simplified Table Extraction, Parsing, and Summarization". It's all being done in T-SQL and it imports files not importable directly, have an unknown number of columns all with unknown names, and some of the columns must be pair with adjacent columns depending on name. Currently, I'm using a 100k Tally table to do the heavy lifting. I just got done testing it... old process with Perl script and some ActiveX took 40 minutes to process two particular files and that was just to get them ready to import... my process actually does everything including the import in 107 seconds and I haven't even tuned the temp tables involved, yet. We import hundreds of such files a day... I'm thinking we'll finally be able to disconnect the garden hose from the server. ;)My mantra in the project has been, "We don' need no stinkin' DTS/SSIS". :hehe:Thu, 26 Feb 2009 13:00:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[font="Verdana"]One of the recent (i.e. in the last ten years) changes in data warehousing has been a move away from specialised ETL tools like Informatica, Data Stage, etc, and towards letting the RDBMS do the work. This approach has been called ELT (extract, load, transform).For smaller data warehouses, this makes sense. There's no need to support two disctinct platforms for throwing data around. It's all done in the RDBMS.Oracle have a product that does that (sorry, can't remember the name), and we use a product at work named Wherescape RED that does the same over the top of SQL Server 2005.So the database does a [b]lot[/b] of work in chopping, changing and reformatting data during the staging process. There's no real presentation layer, as the data just gets posted into another database.That's a [b]very[/b] valid reason to be looking at string operations within an RDBMS, from my perspective.[/font]Thu, 26 Feb 2009 12:43:03 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxGracias.Thu, 26 Feb 2009 07:13:32 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bob Hovious (2/25/2009)[/b][hr]I'm still curious as to why the elapsed times for you (displaying the data) are so dramatically different from the times I get running your code. Any thoughts?[/quote]I have a 7 year old desktop box with server quality cache (whatever that means... salesmen!) but with a single P4 1.8Ghz CPU, a single gig of ram, and twin IDE harddrives. When I boot it, I've got TempDB setup for 2 gig on the MDF and a half gig on the LDF. Dunno if that's it or not. I've found that some laptops do the damndest things especially when it comes to display and execution times.Wed, 25 Feb 2009 20:02:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxI'm still curious as to why the elapsed times for you (displaying the data) are so dramatically different from the times I get running your code. Any thoughts?Wed, 25 Feb 2009 11:06:53 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxThanks Jeff:I realize that I've got a double thread going now, and I just had a light bulb go on when I looked at elapsed time for writing to disk, as opposed to elapsed time for displaying data. Essentially, when displaying data I was seeing elapsed times for simple display being double that of concatenation and I was attributing the bottleneck to the SQL Server. That made me want to stick with concatenation because it was taking 11 seconds to run as opposed to 24. But when I wrote the results to disk (on my laptop), I saw elapsed times similar to yours. So I hit the reset button in my brain.BobWed, 25 Feb 2009 09:14:24 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxThe problem with even some small scale concatenation is... people abuse it and don't know what it actually does to the server. Sure, "It Depends"... but few take the time to figure out "What Depends"... ;)Test data...[code]DROP TABLE JBMTestGO--===== Create and populate a 1,000,000 row test table. -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' -- for all rows. -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F) -- Jeff Moden SELECT TOP 1000000 RowNum = IDENTITY(INT,1,1), SomeInt = ABS(CHECKSUM(NEWID()))%50000+1, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65), SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)), SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY), SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME), SomeHex12 = RIGHT(NEWID(),12) INTO dbo.JBMTest FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2 --===== A table is not properly formed unless a Primary Key has been assigned ALTER TABLE dbo.JBMTest ADD PRIMARY KEY CLUSTERED (RowNum)[/code]Test code...[code] SET STATISTICS TIME ON SELECT t1.SomeInt, STUFF((SELECT ',' + t2.SomeLetters2 FROM dbo.JBMTest t2 WHERE t1.SomeInt = t2.SomeInt FOR XML PATH('')),1,1,'') AS Nodes FROM dbo.JBMTest t1 GROUP BY t1.SomeIntSELECT SomeInt,SomeLetters2FROM dbo.JBMTestSET STATISTICS TIME OFF[/code]Test results...[code](50000 row(s) affected)SQL Server Execution Times: CPU time = 19281 ms, elapsed time = 29566 ms.(1000000 row(s) affected)SQL Server Execution Times: CPU time = 703 ms, elapsed time = 25840 ms.[/code]The concatenation used 27.42 times more cpu and, if you consider the display as the I/O, the concatenation was longer in duration, as well.Even though the concatenation sent about half the number of bytes (because the concatenated operands were so short), which do you suppose is tougher on the server?Tue, 24 Feb 2009 23:06:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx(Sigh... nobody has a sense of humor anymore.)Guys, I've learned to document code out of self-preservation... because [b][i]I[/i][/b] may be the one who has to revisit the code months or years later later. When someone tells me "You ought to be able to look at the code and see what it's doing." my response it always "I can see what it's doing, what I need to know is what it is TRYING to do."My comment wasn't aimed at the comment about documentation, but at the suggestion that we should code to the lowest common denominator. When better solutions come around, we should pass them along, not avoid them because the next guy might not be familiar with them.Thanks again for engaging here. I'm very happy this discussion isn't corrupting the Endless Thread.Tue, 24 Feb 2009 16:12:50 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspx[quote][b]Bob Hovious (2/24/2009)[/b][hr]Ah... but you are denying them the opportunity to broaden their horizons and expand their minds by saying "What the...? How did he DO that???" :D[/quote]Another it depends... what if it takes 5 hours for him to figure it out... while it would take you 2 minutes to document it or code it "better".That's a big value for your employer too...Tue, 24 Feb 2009 15:25:44 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxAh... but you are denying them the opportunity to broaden their horizons and expand their minds by saying "What the...? How did he DO that???" :DTue, 24 Feb 2009 15:10:03 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxBob,Always willing to share my opinion, even when it probably stinks. See, I think that it is necessary to consider others abilities whenever coding something. I want the person that comes behind me, assuming I will eventually move on, to be able to understand my code. I've been the guy coming behind too many times. Certainly if you accurately document your code then it isn't as much of an issue, but I still think it needs to be considered when choosing a solution.Tue, 24 Feb 2009 15:05:15 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxJack, I can see that it would be considered advanced. I never heard about using FOR XML this way until the last couple of months. But having seen it once, the technique is elegantly simple. It is much more intuitive than using a tally table to parse strings. I deliberately sidestepped the issue of other peoples' skills as a consideration in "doing it where it makes sense". Skill levels vary from organization to organization, and for some people, it would take the hours of time to get it right in the UI. You write code on both sides, so you can do it wherever it is easiest for you. So again the answer becomes "it depends". Again, this is just me trying to understand what other considerations should influence the choice. When people I respect say not to do something, it's wise to listen; but it's also important to understand the "why" behind the general rule. The first post contains a number of examples where indisputably the UI should handle string manipulation. But in the case of building delimited strings out of column values for multiple rows, SQL still looks like the better choice.Thanks for the input.Tue, 24 Feb 2009 14:48:05 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxI'm a fan of letting the UI or business layer handle formatting the data and let SQL Server return the data. I know I can do a lot of formatting in T-SQL and I have at times, but that's not what it is best at so I try not to do it there. I'm not a hard-liner on either side, I'm more concerned with making it work and so anyone can read it and debug it. In the example you present I would consider the SQL you have doing the concatenation advanced SQL and most people would not understand it, while doing the concatenation in VB or C# would be a simple loop that any novice programmer could read and debug.Here are a couple of blog posts. The first one on the do it the presentation layer side and the second on the do it in SQL sometimes side. [url]http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/19/sql-tip-keep-your-presentation-logic-in-your-presentation-layer.aspx[/url][url]http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/20/should-i-always-keep-my-presentation-logic-in-my-presentation-layer.aspx[/url]Tue, 24 Feb 2009 13:54:29 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxI'm afraid I can't, but I will listen respectfullly to anyone who can, if they'd care to report their results. However, I see that as a different issue from doing it at the database level instead of passing it to a UI. I can't argue with "It depends." The difference in concatenating and simple passing of the raw data is a function of the average number of rows that need to be concatenated, the average size of the data within the column being concatenated, and the combined size of the columns that would be duplicated. With only two or three average rows being concatenated, the percentage of "compression" would obviously be less. The same would hold true if the values being concatenated were longer strings.Please understand, I'm not arguing that concatenation should [i][b]always[/b][/i] be done at the server, I'm just saying there seem to be instances where it would be the better solution, and more instances where it really doesn't matter. Thanks for the feedback, G.Tue, 24 Feb 2009 12:30:59 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxAs with many things, "it depends". In the situation you outlined, manipulating the values in SQL makes sense.What you might try, if you can, is duplicating the functionality in a CLR proc and see if it's faster at it than the T-SQL version.Tue, 24 Feb 2009 11:13:09 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic663702-49-1.aspxI have heard some criticism of doing concatenation (building delimited strings ) in T-SQL. Two reasons given for this criticism are (1) to protect the resources of the SQL server by farming the load of string manipulation out to the UI, and (2) to "do things where they make sense". Basically, other languages are better at string manipulation and other tasks, so why bother doing it in SQL? Just return the rows and let the UI handle it.This makes obvious sense when building strings. SQL passes a FirstName|LastName|OverdueAmt as separate columns to a UI that can then build the string it wants, whether that string is John SmithSmith, JohnDear John orDear Mr. Smith we currently show your account as having an overdue balance of $250.In the last instance especially, it is foolishly wasteful to add all the constant information at the server then pass it over the network to the application.However, a common requirement is to build a delimited string from a single column value repeated for multiple rows. This is something easily accomplished in T-SQL with very few lines of code, so I consider it to be a draw with respect to string manipulation capability. That leaves the issue of workload on the server.The code at the bottom generates a table of unique first names, last names and department numbers. The schema for the tables containing the first names and last names are provided. I populated them by simply typing in 50 or so names off the top of my head, in some place duplicating last names such as Smith or Jones. But you could just as easily populate them with random strings. Department number is a random number between 1 and 20.The resulting table (#temp2) will be populated with approximately 22,000 unique name/department combinations. The rest of the code compares SQL concatenation of this table with simply returning the values. On my laptop, the concatenation actually runs faster than the simple display of the data. Furthermore, the result set of the concatenation is approximately 1200 rows, instead of over 22,0000. This is, in effect, a compression of the data, because the Firstname/Lastname columns aren't being repeated an additional 20 times. When output was directed to text files, the concatenated version was around 110k in size and the simple display was over a meg. That is nine times the data which has to be buffered and passed to the UI code. If temporary or permanent holding files are the target of this output, there would also be additional logging involved. To me it seems that concatenation at the SQL server may ultimately be reducing the load on the server. What am I missing here?-------------------------------- concatenation test------------------------------set nocount on;--- pseudo random number generation for numbers between 1 and @X SELECT TOP 100000 IDENTITY(INT,1,1) AS RowID, ABS(CHECKSUM(NEWID())) % 50 + 1 AS FKey, ABS(CHECKSUM(NEWID())) % 50 + 1 AS LKey, ABS(CHECKSUM(NEWID())) % 20 + 1 AS Dept INTO #temp FROM Tally; select distinct firstName,lastname,dept into #temp2 from #temp join Lastnames on lastNameID = Lkey join FirstNames on firstNameID = Fkey select count(*) as [#temp2 rows] from #temp2 declare @timer datetimeset @timer = getdate() print 'Display Only'set statistics time on;select * from #temp2 ORDER BY LastName,firstName,deptset statistics time off;print datediff(ms,@timer,getdate()) set @timer = getdate() print 'Concatentation' set statistics time on; select firstName,LastName ,stuff(( SELECT ',' + cast(dept as varchar(2)) FROM #temp2 t2 WHERE t2.LastName = t1.LastName and t2.firstName = t1.firstName -- must match GROUP BY below ORDER BY dept FOR XML PATH('') ),1,1,'') as [Departments]from #temp2 t1GROUP BY LastName,firstName -- without GROUP BY multiple rows are returnedORDER BY LastName,firstName set statistics time off;print datediff(ms,@timer,getdate()) drop table #tempdrop table #temp2Tue, 24 Feb 2009 10:42:28 GMTThe Dixie Flatline