InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 07 Nov 2009 22:27:04 GMT20http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxTrue in both cases. Thanks for the feedback, Sam.Sat, 14 Feb 2009 10:04:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxTo update a batch of many rows at once, you might consider using the .Net SqlBulkCopy class (if you are using .net) class in to a table. It takes a DataSet and given a connection and a table name, will run a bulk insert operation (very very fast).If like me you prefer to have control within a stored procedure, just in case you ever want to add some extra logic, use bulk copy to get the data in to an import table (might be able to use #tables, though have not tried). Then, call the stored proc with no arguments, which could then process the new/updated rows, and empty the import table.SamSat, 14 Feb 2009 09:11:45 GMTsam.walkerhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxSomething you might find useful, as I did, to improve the performance.T-SQL can split fixed width columns much faster than splitting on delimitters.For large csv strings, it is actually more efficient to do a clr function to convert a csv string in to a fixed width string.Egdeclare @csvstr varchar(max)declare @fixedstr varchar(max)declare @fixedlength intset @csvstr = ...set @fixedlength = 20-- convert to csv fixed widthset @fixedstr = dbo.fnCsvToFixedClr(@fcsvstr, @fixedlength)-- now simply split the fixed width string using the tally table (my tally table starts at zero, if your one starts at one use substring(@fixedstr, N * 20 - 19, 20) instead)select substring(@fixedstr, N * 20 + 1, 20) from dbo.Tally where N <= (len(@fixedstr) -1 ) / @fixedlengthSat, 14 Feb 2009 08:55:24 GMTsam.walkerhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThanks for the feedback, Jorge. Let us know if there's anything else we can do to help.Wed, 05 Nov 2008 18:06:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThanks Again Jeff!!I was considering the last option you mentioned. I will do it that way...ThanksJorge LuisTue, 04 Nov 2008 22:19:21 GMTjlcampos71http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxSome will take exception to it, but my feeling is that if it will never me anything but a (-1), then hardcoding it makes it pretty clear when it comes to readability. I would also put a column alias on it just to increase the readability... maybe even with a comment so no one in the future has to guess.. like this (dunno if the comment is correct, though)...[code]--===== Insert the final result of the split -- as a "table" instead of an "EAV" into @Ubicacion INSERT INTO @Ubicacion (ubiIdChip, ubiIdAntena, ubiIdAntenaAnt, ubiFecha, ubiBateria) SELECT MAX(CASE WHEN ColNum = 1 THEN ElementValue END) AS ubiIdChip, MAX(CASE WHEN ColNum = 2 THEN ElementValue END) AS ubiIdAntena, -1 AS ubiIdAntenaAnt, --Previous antena does not exist CAST(CAST(MAX(CASE WHEN ColNum = 3 THEN ElementValue END) AS Float) AS DateTime) AS ubiFecha, MAX(CASE WHEN ColNum = 4 THEN ElementValue END) AS ubiBateria FROM @Elements GROUP BY RowNum[/code]Of course, you could make it self documenting with a variable/constant[code]DECLARE @NothingPrevious INT SET @NothingPrevious = -1--===== Insert the final result of the split -- as a "table" instead of an "EAV" into @Ubicacion INSERT INTO @Ubicacion (ubiIdChip, ubiIdAntena, ubiIdAntenaAnt, ubiFecha, ubiBateria) SELECT MAX(CASE WHEN ColNum = 1 THEN ElementValue END) AS ubiIdChip, MAX(CASE WHEN ColNum = 2 THEN ElementValue END) AS ubiIdAntena, @NothingPrevious AS ubiIdAntenaAnt, CAST(CAST(MAX(CASE WHEN ColNum = 3 THEN ElementValue END) AS Float) AS DateTime) AS ubiFecha, MAX(CASE WHEN ColNum = 4 THEN ElementValue END) AS ubiBateria FROM @Elements GROUP BY RowNum[/code]I don't believe it will matter much for speed either way, but I've not tested this bit of code for performance.Tue, 04 Nov 2008 18:58:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Jeff Moden (10/31/2008)[/b][hr]Jorge,I flattered that you've mimiced the format I use for comments. And, they're pretty much what I would expect to see in a code review.[/quote]Yes, I never though comments should be important in SQL and SPs although I do it a lot while programming in VB.Net, but after reading some of your articles, I realized that it is REALLY Important and nothing better than do it the way the master does. So... Thanks for that.[quote]There are, however, a couple of potential problems with the following code...The comments say that the "Previous antena {will be} set to -1", but I don't see that in the code anywhere.[/quote]I missed that part, there is a place where I set to -1 that column, let me show you... just before the code I pasted here there is this other part of code (commented using the Jeff's way of course)[code]--===== Insert the final result of the split -- as a "table" instead of an "EAV" into @UbicacionINSERT INTO @Ubicacion (ubiIdChip, ubiIdAntena, ubiIdAntenaAnt, ubiFecha, ubiBateria) SELECT MAX(CASE WHEN ColNum = 1 THEN ElementValue END) AS ubiIdChip, MAX(CASE WHEN ColNum = 2 THEN ElementValue END) AS ubiIdAntena, -1, CAST(CAST(MAX(CASE WHEN ColNum = 3 THEN ElementValue END) AS Float) AS DateTime) AS ubiFecha, MAX(CASE WHEN ColNum = 4 THEN ElementValue END) AS ubiBateria FROM @Elements GROUP BY RowNum[/code]As you can see, third value is always a (-1) for the ubiAntenaAnt column, I was wondering if this was the best way or using maybe as a variable set to this value and using it only in the insert clause, maybe that is the best way, because I would reduce the size of that table... let me read what you think... please.[quote]The other problem is that you should probably include a column list in both the Insert and the Select. Some folks call it a "best practice". I won't go that far, but it is something that I make the habit of doing... that way if the import order of columns changes on the temp table, you don't have to worry about data ending up in the wrong columns on the Ubicacion table. It's just one more "safe guard".[/quote]Got it! I think that way too... it was a lazy second I guess!!! Fixed!Thanks again!Jorge LuisTue, 04 Nov 2008 09:26:20 GMTjlcampos71http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]J (10/30/2008)[/b][hr][i]rbarryyoung: (p.s. This is "Humor" also.)[/i]Now, that's funny![/quote]Sitting in a cubicle farm, I tried (unsuccessfully) to not laugh out loud.Thanks -- it made my day. :)Tue, 04 Nov 2008 08:15:43 GMTPaul DBhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxJorge,I flattered that you've mimiced the format I use for comments. And, they're pretty much what I would expect to see in a code review.There are, however, a couple of potential problems with the following code...[code]--===== Insert the values that doesn't exists -- Previous antena set to -1. INSERT INTO Ubicacion SELECT temp.* FROM @Ubicacion temp LEFT JOIN Ubicacion Dest ON Dest.ubiIDChip = temp.ubiIdChip WHERE Dest.ubiIdChip is NULL[/code]The comments say that the "Previous antena {will be} set to -1", but I don't see that in the code anywhere.The other problem is that you should probably include a column list in both the Insert and the Select. Some folks call it a "best practice". I won't go that far, but it is something that I make the habit of doing... that way if the import order of columns changes on the temp table, you don't have to worry about data ending up in the wrong columns on the Ubicacion table. It's just one more "safe guard".Fri, 31 Oct 2008 17:52:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]jlcampos71 (10/31/2008)[/b][hr]I'll do that Jeff... but please let me know... where can I found information about it?[/quote]Yeaup... start by looking up "temporary tables [SQL Server]" (without the quotes) in Books Online. Under the subcategory of "ccreating", they have a section that explains a bit about temp tables. Also, see the following URL...[url]temporary tables [SQL Server][/url]Even thought it's an SQL Server 2000 URL, it's still pretty much spot on.Fri, 31 Oct 2008 17:29:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxI'll do that Jeff... but please let me know... where can I found information about it?As I told you, I'm a very beginner next to you...Is there anything in the BOL or something you have wrote about it?Just to let you know, I have implemented your solution already, After having the @elements table populated, I insert all the data into another table defined as @Ubicacion with the structure I need, and then I do Two different update operations (one condition is managed like a WHERE and the other as an AND in the Join condition) and one INSERT to manage all my diferent cases. Let me show you:[code]--===== Updates the values that already exists and has different antena. -- Updates the value of previous antena with current antena UPDATE Dest SET ubiIdChip = temp.ubiIdChip, ubiIdAntenaAnt = dest.ubiIdAntena, ubiIdAntena = temp.ubiIdAntena, ubiFecha = temp.ubiFecha, ubiBateria = temp.ubiBateria FROM Ubicacion Dest INNER JOIN @Ubicacion temp ON Dest.ubiIDChip = temp.ubiIdChip WHERE dest.ubiIdAntena <> temp.ubiIdAntena --===== Updates the values that already exists and has the same antena. -- Current and Previous antena remains the same. UPDATE Dest SET ubiIdChip = temp.ubiIdChip,-- ubiIdAntenaAnt = ubiIdAntena,-- ubiIdAntena = temp.ubiIdAntena, ubiFecha = temp.ubiFecha, ubiBateria = temp.ubiBateria FROM Ubicacion Dest INNER JOIN @Ubicacion temp ON Dest.ubiIDChip = temp.ubiIdChip AND Dest.ubiIdAntena = temp.UbiIdAntena--===== Insert the values that doesn't exists -- Previous antena set to -1. INSERT INTO Ubicacion SELECT temp.* FROM @Ubicacion temp LEFT JOIN Ubicacion Dest ON Dest.ubiIDChip = temp.ubiIdChip WHERE Dest.ubiIdChip is NULL[/code]Would you mind to review it?Thanks again!Jorge LuisFri, 31 Oct 2008 08:56:20 GMTjlcampos71http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]jlcampos71 (10/29/2008)[/b][hr]Thanks Again Jeff!I almost Figured that out... but I didn't at all... I was figuring out that I should make in fact, 3 passes, because there is a second constriction in case the key already exists... But yes, you are right... my SP would take the data from the Split table (@Elements) and insert or update according to your suggestion.Thanks again!!!Jorge Luis[/quote]You bet, Jorge. Thanks for the feeback.Also, my recommendation would to see what happens to performance if you convert @Elements to #Elements... ;)Thu, 30 Oct 2008 18:46:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[i]rbarryyoung: (p.s. This is "Humor" also.)[/i]Now, that's funny!Thu, 30 Oct 2008 06:16:30 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThanks Again Jeff!I almost Figured that out... but I didn't at all... I was figuring out that I should make in fact, 3 passes, because there is a second constriction in case the key already exists... But yes, you are right... my SP would take the data from the Split table (@Elements) and insert or update according to your suggestion.Thanks again!!!Jorge LuisWed, 29 Oct 2008 22:28:37 GMTjlcampos71http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]jlcampos71 (10/29/2008)[/b][hr]This "Passing Parameters" Stuff is great... it almost solves my problem...I might sound like a beginner next to you but this is the best I have found...I have a table that I need to update with a lot of rows comming very fast. I think I can group this rows and pass them to a Stored Procedure every some seconds as an "array" or a parameter of 2 dimensions.Everything looks find with your article... I Already have a table with all the incomming data in SQL, the problem is that I should insert a new row only if the key doesn't exists in the table, and update the row if the key already exists.Do you have any ideas on how to do this without using a loop to call a SP to do this?After reading some of your articles I already know that I should review all my code in order to tune up my SQL server, but any tip on this could be very helpful.Thanks in advanceJorge Luis[/quote]Thanks, Jorge. Greate compliment.You wouldn't need a Split function for your particlar problem , though. Nor would you need to pass parameters. You would simply build an Insert to insert rows from the source to target tables that didn't already exist and an Update to update the ones that did. 2 Complete passes... no loops.Wed, 29 Oct 2008 22:11:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThis "Passing Parameters" Stuff is great... it almost solves my problem...I might sound like a beginner next to you but this is the best I have found...I have a table that I need to update with a lot of rows comming very fast. I think I can group this rows and pass them to a Stored Procedure every some seconds as an "array" or a parameter of 2 dimensions.Everything looks find with your article... I Already have a table with all the incomming data in SQL, the problem is that I should insert a new row only if the key doesn't exists in the table, and update the row if the key already exists.Do you have any ideas on how to do this without using a loop to call a SP to do this?After reading some of your articles I already know that I should review all my code in order to tune up my SQL server, but any tip on this could be very helpful.Thanks in advanceJorge LuisWed, 29 Oct 2008 21:35:50 GMTjlcampos71http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxJeff: just because they're not out to get you, doesn't mean that you can't be paranoid.(p.s. This is "Humor" also.):DWed, 29 Oct 2008 21:07:31 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxDamn! That's two times I missed the intended humor... ok... I'm going to my room to suck my thumb and twiddle my hair. :PWed, 29 Oct 2008 18:23:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxJeff,I was myself one-upping the while loop joke, this time with actual real life examples, not even a joke. [i](Hah ! Amateurs!)[/i].Regards all.Wed, 29 Oct 2008 06:46:34 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxJ... I misread Gary's post... it was all actually a pretty nice compliment. :) The "While-loop Hole" wasn't a crack at me... it was his way of saying that he could now flush most While-loops "down the hole". :DTue, 28 Oct 2008 21:30:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[i]Gary Noter [snip] I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!![/i]OK, two things on this,1. I remember reading about a BIG computer company where one brilliant project manager complained that some outside programmers had replaced two thousand lines of buggy code with a 300-liner that did the job. The manager complained that this was a "negative contribution to the number of code lines per day". 2. And also this one, a programmer who used a text processor to "unroll" a while loop intended to add one thousand numbers by writing a linear set of 1,000 successive additions. That one did get it! Time-consuming while loops. Hah! Amateurs!This is how, when you set metrics, you are likely to deserve the results you get.Tue, 28 Oct 2008 07:24:37 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxI moved my feedback to a more [b][url=http://www.sqlservercentral.com/Forums/Topic496042-203-19.aspx]appropiate topic[/url][/b].Thu, 25 Sep 2008 04:29:44 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Gary Noter (7/28/2008)[/b][hr]Uhmm, Jeff, moi post was humor; or did I miss that you didn't miss the humor, or did you miss the humor (or are we now in a WHILE loop [per se]? Cuz I (now) do use yer Tally table stuff & passing parameters, etc. Thx!![/quote]Heh... was a really bad day for me and I [i]totally [/i]missed the nice dry humor, Gary. Thanks for the compliment and I'm really sorry I took it the wrong way. :)Mon, 28 Jul 2008 17:54:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Jeff Moden (7/24/2008)[/b][hr][quote][b]Gary Noter (7/24/2008)[/b][hr]This example code is a TOTAL waste of my time.No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!![/quote]It's totally obvious that you didn't actually read the article. The WHILE loops were an example of how NOT to do it. ;)[/quote]Uhmm, Jeff, moi post was humor; or did I miss that you didn't miss the humor, or did you miss the humor (or are we now in a WHILE loop [per se]? Cuz I (now) do use yer Tally table stuff & passing parameters, etc. Thx!!Mon, 28 Jul 2008 10:40:28 GMTGary Noterhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Gary Noter (7/24/2008)[/b][hr]This example code is a TOTAL waste of my time.No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!![/quote]It's totally obvious that you didn't actually read the article. The WHILE loops were an example of how NOT to do it. ;)Thu, 24 Jul 2008 17:40:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThis example code is a TOTAL waste of my time.No, wait, if I implement this code, then can't waste my time with writing and waiting for WHILE loops to execute and bill my customers for more time.Therefore, it is a waste of getting *MORE* billable time (or actually maintaining it)!Shhh, I'll implement this code and I'll still bill them the time I used to spend waiting for the WHILE loop to execute. I'll call this the "WHILE Loop-hole" in my Billing Secrets blackbook !!!Thu, 24 Jul 2008 13:01:10 GMTGary Noterhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThanks for the links and info, Tom... I'll take a look.Sat, 31 May 2008 16:06:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Jeff Moden (5/30/2008)[/b][hr]Howdy folks,Someone who wishes to remain anonymous, send me an email asking the following questions related to this article. I've not worked with SQL Server 2008 (shoot, I'm just getting up to speed on 2k5) so I can't answer them... sure could use some help if you have a minute. Here's the questions I was asked...[i]1. (concerning 2k8) One feature, I believe I saw, was the ability to pass multidimensional arrays in the procedures. Is it as easy as setting a “@myTable As table” for a passed in parameter to a stored proc? 2. ... since table variables are already used in stored procedures, is this a feature more valuable in Visual Studio and should be discussed in that arena? 3. As a side note: I have heard Microsoft purchased Dundas reports and believe have seen it is bundled in Reporting Services 2008. If I begin working with the community releases of SQL 2008, will those reports be available already? [/i]Thanks for any information you may have on these questions.[/quote]Jeff,I remember reading about tables as parameters, and my perhaps faulty memory was that they could only be used within T-SQL, not from external calls.I did a little googling today to try to update my memory, and this TechNet article [url=http://technet.microsoft.com/en-us/library/bb510489(SQL.100).aspx][b]Table-Valued Parameters (Database Engine)[/b][/url] states:[quote]You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language. [/quote]Then I found this MSDN Library Item..[url=http://msdn.microsoft.com/en-us/library/bb675218.aspx][b]New Features in SQL Server (ADO.NET)[/b][/url] with some sample .NET code.The coding for creating the parameter is not as straight forward as you would guess, so for sub or nested procedures the temp table will still be easier to use in most cases.Sat, 31 May 2008 13:49:02 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxHowdy folks,Someone who wishes to remain anonymous, send me an email asking the following questions related to this article. I've not worked with SQL Server 2008 (shoot, I'm just getting up to speed on 2k5) so I can't answer them... sure could use some help if you have a minute. Here's the questions I was asked...[i]1. (concerning 2k8) One feature, I believe I saw, was the ability to pass multidimensional arrays in the procedures. Is it as easy as setting a “@myTable As table” for a passed in parameter to a stored proc? 2. ... since table variables are already used in stored procedures, is this a feature more valuable in Visual Studio and should be discussed in that arena? 3. As a side note: I have heard Microsoft purchased Dundas reports and believe have seen it is bundled in Reporting Services 2008. If I begin working with the community releases of SQL 2008, will those reports be available already? [/i]Thanks for any information you may have on these questions.Fri, 30 May 2008 22:43:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThis 2D stuff is excellent for normalizing 1NF (first normal form) violations like '123^12|456^45'|789^12|945^34'2D array parsing without table variables or temp tables! It is using Itzik Ben-Gan's parsing algorithm that relies on a table of numbers (counter / tally / nums). My version of the 2D enhancement uses CROSS APPLY so it doesn't work in SQL Server 2000.[b]2D 'Table' version - outputs vertical-ized data only; faster but not very useful on 2D data:[/b][code]--Normal VarChar versionCREATE FUNCTION dbo.fn_DelimitToTable_2D ( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLEASRETURN ( SELECT Counter2nd.Value AS Value FROM ( SELECT SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT SUBSTRING(Counter1st.Value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.Value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.Value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd )GO--Integer casting version when output is used to join to integer PK/FK columns.CREATE FUNCTION dbo.fn_DelimitToIntTable_2D ( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLEASRETURN ( SELECT CONVERT(int, Counter2nd.Value) AS PK_IntID FROM ( SELECT SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT SUBSTRING(Counter1st.Value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.Value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.Value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd )GO[/code][b]'Array' version - outputs indexer also (more overhead):[/b][code]--Normal VarChar versionCREATE FUNCTION dbo.fn_DelimitToArray_2D ( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLEASRETURN ( SELECT Counter1st.Pos AS RowPos, Counter2nd.Pos AS ColPos, Counter2nd.Value AS Value FROM ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter1, '')) AS Pos, SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT PK_CountID - LEN(REPLACE(LEFT(Counter1st.Value, PK_CountID-1), @Delimiter2, '')) AS Pos, SUBSTRING(Counter1st.Value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.Value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.Value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd )GO--Integer casting version when output is used to join to integer PK/FK columns.CREATE FUNCTION dbo.fn_DelimitToIntArray_2D ( @String VarChar(8000), @Delimiter1 VarChar(1), @Delimiter2 VarChar(1) ) RETURNS TABLEASRETURN ( SELECT Counter1st.Pos AS RowPos, Counter2nd.Pos AS ColPos, CONVERT(int, Counter2nd.value) AS PK_IntID FROM ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter1, '')) AS Pos, SUBSTRING(@String+@Delimiter1, PK_CountID, CHARINDEX(@Delimiter1, @String+@Delimiter1, PK_CountID)-PK_CountID) AS value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter1) AND SubString(@Delimiter1 + @String + @Delimiter1, PK_CountID, 1)=@Delimiter1 ) AS Counter1st CROSS APPLY ( SELECT PK_CountID - LEN(REPLACE(LEFT(Counter1st.value, PK_CountID-1), @Delimiter2, '')) AS Pos, SUBSTRING(Counter1st.value+@Delimiter2, PK_CountID, CHARINDEX(@Delimiter2, Counter1st.Value+@Delimiter2, PK_CountID)-PK_CountID) AS value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(Counter1st.value)+LEN(@Delimiter2) AND SubString(@Delimiter2 + Counter1st.value + @Delimiter2, PK_CountID, 1)=@Delimiter2 ) AS Counter2nd )GO[/code]For those of you who don't have Itzik Ben-Gan's Inside SQL Server 2005 T-SQL books or been to any of his conference sessions (the books are a lot cheaper), here are 1D versions:[b]'Table' version - ordinal postion stripped out for speed; Great for stored-procedure-izing IN() clauses - WHERE id IN (1,2,3,4):[/b][code]--Normal VarChar versionCREATE FUNCTION dbo.fn_DelimitToTable ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLEASRETURN ( SELECT SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter )GO--Integer casting version when output is used to join to integer PK/FK columns.CREATE FUNCTION dbo.fn_DelimitToIntTable ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLEASRETURN ( SELECT CONVERT(int, SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID)) AS PK_IntID FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter )GO[/code][b]'Array' version - with position indexer - good for index change scripts where column-order matters:[/b][code]--Normal VarChar versionCREATE FUNCTION dbo.fn_DelimitToArray ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLEASRETURN ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter, '')) AS Pos, SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID) AS Value FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter )GO--Integer casting version when output is used to join to integer PK/FK columns.CREATE FUNCTION dbo.fn_DelimitToIntArray ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLEASRETURN ( SELECT PK_CountID - LEN(REPLACE(LEFT(@String, PK_CountID-1), @Delimiter, '')) AS Pos, CONVERT(int, SUBSTRING(@String+@Delimiter, PK_CountID, CHARINDEX(@Delimiter, @String+@Delimiter, PK_CountID)-PK_CountID)) AS PK_IntID FROM dbo.counter WHERE PK_CountID >0 AND PK_CountID<LEN(@String)+LEN(@Delimiter) AND SubString(@Delimiter + @String + @Delimiter, PK_CountID, 1)=@Delimiter )[/code][b]As for logical reads on the nums / tally / counter table:[/b]SQL server 2005 can fit 622 numbers per page if it is clustered. That drops to 299 if it is a heap. SQL Server 2000 can fit 620 numbers per page clustered.1 I/O per hit guaranteed: 299-number heap (seek or scan; only tested in 2005)2 I/Os per hit guaranteed (seek or scan): 622 number clustered (620 for 2000)Fully packed 2-level clustered index for a 2 I/O minimum per seek: 386,884 numbers (384,400 for 2000)Make sure you use a 100% fill facter (the data shouldn't ever change), and after populating the tables with data, you do a rebuild:ALTER INDEX ALL ON Counter REBUILD WITH (FillFactor=100) for SQL Server 2005DBCC DBREINDEX (Counter,'PK_C_IX__Counter__CountID',100) for SQL Server 2000I usually use both a 'small' version and a 'standard' version of the table of numbers (counter / nums / tally). Never needed the 'big' version yet - a fully packed 3-level clustered index with 240,641,848 numbers (238,328,000 for SQL2000).Here is my counter table building script for SQL Server 2005 and 2000; it runs in 4 seconds and allows or having a portion of your numbers being negative. @MaxPositive and @ClusteredRowsPerPage are the hard-coded controlling parameters.[b]1-Level, 2-Level, and 3-Level (commented) Counter / Tally / Nums table builder SQL Server 2005:[/b][code]--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=--DDL--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=SET NOCOUNT ON--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=IF EXISTS (SELECT * FROM sys.tables WHERE name='CounterSmall' AND schema_id=1) DROP TABLE dbo.CounterSmallIF EXISTS (SELECT * FROM sys.tables WHERE name='Counter' AND schema_id=1) DROP TABLE dbo.Counter--IF EXISTS (SELECT * FROM sys.tables WHERE name='CounterBig' AND schema_id=1) DROP TABLE dbo.CounterBigGO--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=CREATE TABLE dbo.CounterSmall( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterSmall__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100)CREATE TABLE dbo.Counter( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__Counter__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100)/*CREATE TABLE dbo.CounterBig( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterBig__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100)*/GO--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=--Counter SQL 2005--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=DECLARE @Power intDECLARE @HeapRowsPerPage intDECLARE @ClusteredRowsPerPage intDECLARE @MaxRows intDECLARE @MaxPositive intDECLARE @MaxNegative intDECLARE @OldMaxNegative intSET @ClusteredRowsPerPage=622SET @HeapRowsPerPage=299SET @MaxPositive=621--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=SET @MaxRows=@ClusteredRowsPerPageSET @MaxPositive=@MaxPositive-1SET @OldMaxNegative=0SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegativeSET @Power=1PRINT 'CounterSmall: ' + CONVERT(VarChar(10), @MaxNegative*-1+1) + ' to ' + CONVERT(VarChar(10), @MaxPositive) + ' - ' + CONVERT(VarChar(10), @MaxRows) + ' Rows - 1-Level Clustered Index'--SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRowsTRUNCATE TABLE CounterSmallBEGIN TRANSACTION/*INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID)SELECT PK_CountID-@MaxNegativeFROM dbo.fn_Numbers(@MaxRows)*/INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) VALUES (1-@MaxNegative)WHILE @Power<=@MaxRowsBEGIN INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterSmall WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2ENDCOMMITALTER INDEX ALL ON CounterSmall REBUILD WITH (FillFactor=100)UPDATE STATISTICS CounterSmall WITH FULLSCAN--SELECT * FROM CounterSmall--*=*=*=*=*=*=*=*=*=*=SET @Power=@ClusteredRowsPerPageSET @MaxRows=@Power*@ClusteredRowsPerPageSET @OldMaxNegative=@MaxNegative+@OldMaxNegativeSET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPageSET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegativePRINT 'Counter: ' + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ' to ' + CONVERT(VarChar(10), @MaxPositive) + ' - ' + CONVERT(VarChar(10), @MaxRows) + ' Rows - 2-Level Clustered Index'--SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRowsTRUNCATE TABLE CounterBEGIN TRANSACTIONINSERT INTO Counter WITH (TABLOCKX) (PK_CountID)SELECT PK_CountID-@MaxNegative FROM CounterSmallWHILE @Power<=@MaxRowsBEGIN INSERT INTO Counter WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM Counter WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2ENDCOMMITALTER INDEX ALL ON Counter REBUILD WITH (FillFactor=100)UPDATE STATISTICS Counter WITH FULLSCAN--SELECT * FROM Counter ORDER BY PK_CountID--*=*=*=*=*=*=*=*=*=*=/*SET @Power=@ClusteredRowsPerPage*@ClusteredRowsPerPageSET @MaxRows=@Power*(@ClusteredRowsPerPage-2)SET @OldMaxNegative=@MaxNegative+@OldMaxNegativeSET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPageSET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegativePRINT 'CounterBig: ' + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ' to ' + CONVERT(VarChar(10), @MaxPositive) + ' - ' + CONVERT(VarChar(10), @MaxRows) + ' Rows - 3-Level Clustered Index'--SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRowsTRUNCATE TABLE CounterBigUPDATE STATISTICS CounterBig WITH FULLSCAN, NORECOMPUTEBEGIN TRANSACTIONINSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID)SELECT PK_CountID-@MaxNegative FROM CounterWHILE @Power<=@MaxRowsBEGIN INSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterBig WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2ENDCOMMITALTER INDEX ALL ON CounterBig REBUILD WITH (FillFactor=100)UPDATE STATISTICS CounterBig WITH FULLSCAN--SELECT * FROM CounterBig ORDER BY PK_CountID*/--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('CounterSmall'), NULL, NULL, 'DETAILED')SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('Counter'), NULL, NULL, 'DETAILED')--SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('CounterBig'), NULL, NULL, 'DETAILED')--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=GO[/code][b]1-Level, 2-Level, and 3-Level (commented) Counter / Tally / Nums table builder SQL Server 2000:[/b][code]--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=--DDL--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=SET NOCOUNT ON--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=IF EXISTS (SELECT * FROM sysobjects WHERE name='CounterSmall' AND uid=1 AND xtype='u') DROP TABLE dbo.CounterSmallIF EXISTS (SELECT * FROM sysobjects WHERE name='Counter' AND uid=1 AND xtype='u') DROP TABLE dbo.Counter--IF EXISTS (SELECT * FROM sysobjects WHERE name='CounterBig' AND uid=1 AND xtype='u') DROP TABLE dbo.CounterBig--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=CREATE TABLE dbo.CounterSmall( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterSmall__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100)CREATE TABLE dbo.Counter( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__Counter__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100)/*CREATE TABLE dbo.CounterBig( PK_CountID int NOT NULL, CONSTRAINT PK_C_IX__CounterBig__CountID PRIMARY KEY CLUSTERED (PK_CountID) WITH FILLFACTOR=100)*/--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=--Counter SQL 2000--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=DECLARE @Power intDECLARE @HeapRowsPerPage intDECLARE @ClusteredRowsPerPage intDECLARE @MaxRows intDECLARE @MaxPositive intDECLARE @MaxNegative intDECLARE @OldMaxNegative intSET @ClusteredRowsPerPage=620SET @HeapRowsPerPage=299SET @MaxPositive=619--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=SET @MaxRows=@ClusteredRowsPerPageSET @MaxPositive=@MaxPositive-1SET @OldMaxNegative=0SET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegativeSET @Power=1PRINT 'CounterSmall: ' + CONVERT(VarChar(10), @MaxNegative*-1+1) + ' to ' + CONVERT(VarChar(10), @MaxPositive) + ' - ' + CONVERT(VarChar(10), @MaxRows) + ' Rows - 1-Level Clustered Index'--SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRowsTRUNCATE TABLE CounterSmallBEGIN TRANSACTIONINSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) VALUES (1-@MaxNegative)WHILE @Power<=@MaxRowsBEGIN INSERT INTO CounterSmall WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterSmall WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2ENDCOMMITDBCC DBREINDEX (CounterSmall,'PK_C_IX__CounterSmall__CountID',100)UPDATE STATISTICS CounterSmall WITH FULLSCAN--SELECT * FROM CounterSmall--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=SET @Power=@ClusteredRowsPerPageSET @MaxRows=@Power*@ClusteredRowsPerPageSET @OldMaxNegative=@MaxNegative+@OldMaxNegativeSET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPageSET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegativePRINT 'Counter: ' + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ' to ' + CONVERT(VarChar(10), @MaxPositive) + ' - ' + CONVERT(VarChar(10), @MaxRows) + ' Rows - 2-Level Clustered Index'--SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRowsTRUNCATE TABLE CounterBEGIN TRANSACTIONINSERT INTO Counter WITH (TABLOCKX) (PK_CountID)SELECT PK_CountID-@MaxNegative FROM CounterSmallWHILE @Power<=@MaxRowsBEGIN INSERT INTO Counter WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM Counter WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2ENDCOMMITDBCC DBREINDEX (Counter,'PK_C_IX__Counter__CountID',100)UPDATE STATISTICS Counter WITH FULLSCAN--SELECT * FROM Counter ORDER BY PK_CountID--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=/*SET @Power=@ClusteredRowsPerPage*@ClusteredRowsPerPageSET @MaxRows=@Power*(@ClusteredRowsPerPage-2)SET @OldMaxNegative=@MaxNegative+@OldMaxNegativeSET @MaxPositive=(@MaxPositive+1)*@ClusteredRowsPerPageSET @MaxNegative=@MaxRows-@MaxPositive-@OldMaxNegativePRINT 'CounterBig: ' + CONVERT(VarChar(10), @MaxNegative*-1-@OldMaxNegative+1) + ' to ' + CONVERT(VarChar(10), @MaxPositive) + ' - ' + CONVERT(VarChar(10), @MaxRows) + ' Rows - 3-Level Clustered Index'--SELECT @MaxNegative AS MaxNegative, @MaxPositive AS MaxPositive, @OldMaxNegative AS OldMaxNegative, @Power AS Power, @MaxRows AS MaxRowsTRUNCATE TABLE CounterBigUPDATE STATISTICS CounterBig WITH FULLSCAN, NORECOMPUTEBEGIN TRANSACTIONINSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID)SELECT PK_CountID-@MaxNegative FROM CounterWHILE @Power<=@MaxRowsBEGIN INSERT INTO CounterBig WITH (TABLOCKX) (PK_CountID) SELECT @Power+PK_CountID FROM CounterBig WHERE @Power+PK_CountID<=@MaxPositive SET @Power=@Power*2ENDCOMMITDBCC DBREINDEX (Counter,'PK_C_IX__CounterBig__CountID',100)UPDATE STATISTICS CounterBig WITH FULLSCAN--SELECT * FROM CounterBig ORDER BY PK_CountID*/--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=DBCC SHOWCONTIG (CounterSmall) WITH ALL_LEVELS, TABLERESULTSDBCC SHOWCONTIG (Counter) WITH ALL_LEVELS, TABLERESULTS--DBCC SHOWCONTIG (CounterBig) WITH ALL_LEVELS, TABLERESULTS--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=[/code]I have big versions and two-column versions as well, but the post is already too big. The big version gracefully can handle more than hundreds of thousands of characters because it splices into 8000 character blocks. More code, no longer an inline table-valued function (inline table-valued functions are processed as derived tables / views behind the scenes and are much faster), but it is faster than VarChar(max) and works in SQL Server 2000 (if the string input is text instead of VarChar(max)) and never uses more than 8000 numbers.I have had other uses for a table of numbers, particularly reporting involving date-ranges and you want to show a date-range-block even if there is no data with a date within that date-range block.Thu, 29 May 2008 15:53:01 GMTYeshuaAgapaohttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Jeff Moden (5/24/2008)[/b][hr][quote][b]Matt Miller (5/23/2008)[/b][hr]The funny clothes I can deal with. The tin foil hat on the other hand...:)[/quote]Heh! C'mon... I don't wear the hat in public... and no one can see the lead cods piece... :hehe:[/quote]now there goes a visual I didn't need. I am going to have to double myt alcohol intake tomorrow just to flush that right out...:w00t:Sun, 25 May 2008 14:28:55 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Matt Miller (5/23/2008)[/b][hr]The funny clothes I can deal with. The tin foil hat on the other hand...:)[/quote]Heh! C'mon... I don't wear the hat in public... and no one can see the lead cods piece... :hehe:Sat, 24 May 2008 16:17:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote] This last article, if I had to guess, took me somewhere between 12 and 16 hours to write. A more prolific writer could probably have banged it out in an hour or two and still done a heck of a good job.[/quote] Still if you are not profilic writic then we should change the profilic writer's definition................;) Sat, 24 May 2008 06:52:11 GMTAnirban Paulhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Jeff Moden (5/23/2008)[/b][hr]Oh heck no... those are two 8 hour days... gotta earn a living and a book isn't likely to be a great source of income... HEH! If you don't believe that, look at Celko... he's got a number of books on the market and he [i]still [/i]dresses funny... :D[/quote]The funny clothes I can deal with. The tin foil hat on the other hand...:)Fri, 23 May 2008 22:21:35 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Matt Miller (5/23/2008)[/b][hr][quote][b]Jeff Moden (5/23/2008)[/b][hr]Heh... just imagine me writing a book... 11 pages every 2 days... it would take an eon for me to write one. I don't know how some of these folks can turn out a couple of 500 or 1,000 page books a year... I envy them, quite a bit.[/quote]Hmm Jeff. Food for thought. If you can crank 11 pages in 2 days:11 pages every 2 days = 5.5pages per day.So 750 pages ==> 27 weeks at 5 days a week (and we all know you don't walk away on weekends...). So - I take it that means we can be expecting 2 books a year from you? :w00t:[/quote]Oh heck no... those are two 8 hour days... gotta earn a living and a book isn't likely to be a great source of income... HEH! If you don't believe that, look at Celko... he's got a number of books on the market and he [i]still [/i]dresses funny... :DFri, 23 May 2008 17:28:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThat's some of the best compliments I could hope for, Tom... someone adding this type of code to their library. Thanks!If you haven't done so already, here's a recent article I wrote about how the Tally table works to replace loops, in some cases...[url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url]... and, if you get really bored on this fine 3 day weekend, here's all my stuff, so far. The one on running balances has some pretty neat stuff that can be used for other, surprising things...[url]http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/[/url]What're even more worth reading are the great suggestions some folks wrote about in the discussions that followed each article and the wonderful code examples some folks submitted as a part of the discussions.Fri, 23 May 2008 15:22:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxThanks Jeff, for some more COOL STUFF.Most days I don't have time to read the more lengthy articles, but I did today, and I've been meaning to look into these Tally thingies you keep talking about, and see how they actually have some usefulness.That's some great, and efficient code, and some of it is now in my "library".Thanks again,Fri, 23 May 2008 11:00:19 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Jeff Moden (5/23/2008)[/b][hr]Heh... just imagine me writing a book... 11 pages every 2 days... it would take an eon for me to write one. I don't know how some of these folks can turn out a couple of 500 or 1,000 page books a year... I envy them, quite a bit.[/quote]Hmm Jeff. Food for thought. If you can crank 11 pages in 2 days:11 pages every 2 days = 5.5pages per day.So 750 pages ==> 27 weeks at 5 days a week (and we all know you don't walk away on weekends...). So - I take it that means we can be expecting 2 books a year from you? :w00t:Fri, 23 May 2008 09:49:48 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic499145-203-1.aspx[quote][b]Anirban Paul (5/23/2008)[/b][hr]Awesome one Jeff. Another bomb. I just wonder how much pain you take to write these articles? Excellent one. Keep it up......:)[/quote]Thanks for the great feedback and the question, too, Arniban!I'm not what I'd call a "prolific" writer... I agonize over everything... form, fit, function, correctness and readability of code, order of presentation, etc. The code is especially important... I hate it when I use someone else's code and it doesn't work as advertised or it's difficult to read and has no embedded documentation. The code examples I create are typically very simple, but they have to follow my own rules... "Make it work, make it fast, make it pretty, and it ain't done 'til it's pretty."I also hate it when someone's graphics are too small to read and, when you zoom in on them, too blurry to easily read.This last article, if I had to guess, took me somewhere between 12 and 16 hours to write. A more prolific writer could probably have banged it out in an hour or two and still done a heck of a good job.Heh... just imagine me writing a book... 11 pages every 2 days... it would take an eon for me to write one. I don't know how some of these folks can turn out a couple of 500 or 1,000 page books a year... I envy them, quite a bit.Fri, 23 May 2008 08:48:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic499145-203-1.aspxAwesome one Jeff. Another bomb. I just wonder how much pain you take to write these articles? Excellent one. Keep it up......:)Fri, 23 May 2008 05:31:36 GMTAnirban Paul