SQLServerCentral / Discuss Content Posted by Michael Coles / Article Discussions / Article Discussions by Author / The Joy of Numbers / Latest Posts

RE: The Joy of Numbers

Jeff Moden — Sat, 21 Aug 2010 11:11:38 GMT

[quote][b]_ms65g_ (8/17/2010)[/b][hr]Hi,Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…See:[code="sql"]DECLARE @s VARCHAR(620)='item_01,item_02,item_03';SET @s = ','+ @s + ',';;WITH C AS(SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn FROM Nums --Number Table WHERE SUBSTRING(@s, n, 1) = ',' AND n <= LEN(@s))SELECT SUBSTRING(@s, A.n + 1, B.n - 1 - A.n) AS item FROM C A INNER JOIN C B ON A.rn + 1 = B.rn AND B.n - A.n > 1;[/code][/quote]That method is quite old, actually. Most people don't use it because, since it actually has to read the numbers table twice, it's usually slower than the CHARINDEX method.If you'd like to convert the code (I don't want to be accused of writting biased code :-D) so that it can be played against a table with a CSV column in it, I'd be happy to demonstrate/backup my claim with code.

RE: The Joy of Numbers

_ms65g_ — Tue, 17 Aug 2010 10:24:07 GMT

Hi,Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…See:[code="sql"]DECLARE @s VARCHAR(620)='item_01,item_02,item_03';SET @s = ','+ @s + ',';;WITH C AS(SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn FROM Nums --Number Table WHERE SUBSTRING(@s, n, 1) = ',' AND n <= LEN(@s))SELECT SUBSTRING(@s, A.n + 1, B.n - 1 - A.n) AS item FROM C A INNER JOIN C B ON A.rn + 1 = B.rn AND B.n - A.n > 1;[/code]Also here is the simplified version of my technique for removing duplicate side-by-side characters.[code="sql"] DECLARE @result VARCHAR(8000) = '', @string VARCHAR(8000) = 'SQQQQQQQQQQLLLLLLL Serrrrrrrrveerr'; SELECT @result = @result + Data FROM (SELECT ID, Data, ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) - ID FROM (SELECT SUBSTRING(@String, n, 1), n FROM Nums WHERE n <= LEN(@String) ) D(data, ID) ) D(ID, Data, RowNum) GROUP BY Data, RowNum ORDER BY MIN(ID) SELECT @result AS Result[/code]

RE: The Joy of Numbers

Mike C — Mon, 09 Aug 2010 15:30:23 GMT

[quote][b]_ms65g_ (5/28/2010)[/b][hr]Remove duplicate side-by-side characters from a stringNew approach using numbers table[code="sql"]CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) RETURNS VARCHAR(8000) ASBEGIN DECLARE @result VARCHAR(8000) = ''; ;WITH DataOrder AS ( SELECT ID, Data ,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum FROM (SELECT SUBSTRING(@String, nbr, 1), nbr FROM Nums WHERE nbr <= LEN(@String) ) D(data, ID) ) SELECT @result = @result + Data FROM (SELECT ID, Data ,DENSE_RANK() OVER (ORDER BY ID - RowNum) As [Rank] FROM DataOrder )D GROUP BY Data, [Rank] ORDER BY MIN(ID) RETURN @resultEND;[/code][/quote]Be careful with the multirow concatenation thing you got going on there. You might be better off using the FOR XML PATH subquery method for ordered string concatenation (but then you have to worry about XML entities).ThanksMike C

RE: The Joy of Numbers

_ms65g_ — Fri, 28 May 2010 03:27:35 GMT

Remove duplicate side-by-side characters from a stringNew approach using numbers table[code="sql"]CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) RETURNS VARCHAR(8000) ASBEGIN DECLARE @result VARCHAR(8000) = ''; ;WITH DataOrder AS ( SELECT ID, Data ,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum FROM (SELECT SUBSTRING(@String, nbr, 1), nbr FROM Nums WHERE nbr <= LEN(@String) ) D(data, ID) ) SELECT @result = @result + Data FROM (SELECT ID, Data ,DENSE_RANK() OVER (ORDER BY ID - RowNum) As [Rank] FROM DataOrder )D GROUP BY Data, [Rank] ORDER BY MIN(ID) RETURN @resultEND;[/code]

RE: The Joy of Numbers

Jeff Moden — Sun, 23 May 2010 18:05:47 GMT

[quote][b]_ms65g_ (5/23/2010)[/b][hr]Thank you, But I do not mean publishing number table, I mean Splitting method is not simpler?Also, A simplified method for publishing number table (no loop, no recursion, and no ranking)[code="sql"];WITH C (i) AS(SELECT '0' UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5'UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9')SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4[/code][/quote]Oh be careful... That method will always generate all 10000 numbers even it you put a filter on it and it has the added disadvantage of not producing an ordered output. Compare the actual execution plans of your method and a derivative of Itzik Ben-Gan's method...[code="sql"]--===== This method causes all 10000 rows to show up in the execution plan even thouth only 100 -- are called for. It also has the disadvantage of not being sorted. I wouldn't use this method.;WITH C (i) AS( SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9'),cteTally AS(SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4) SELECT nbr FROM cteTally t WHERE nbr BETWEEN 1 AND 100 ;--===== Although a bit more complicated, this method is very effective because it only generates the -- number of rows requested AND has the advantage of a sorted result set.WITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4) SELECT N FROM cteTally WHERE N BETWEEN 1 AND 100[/code]Also, although it doesn't appear to hurt anything, the split code you posted has an unnecessary SELECT in it. Take a look...[code="sql"]DECLARE @s VARCHAR(80);SELECT @s = 'Army,Navy,Air Force,Marines';--===== Your splitter with Itzik's inline Tally tableWITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4) --===== Do your split SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM cteTally WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ',';--===== An even simpler splitter, also with Itzik's inline Tally tableWITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4) --===== Do the split SELECT SUBSTRING(@s, N, CHARINDEX(',', @s + ',', N) - N) AS Item FROM cteTally WHERE N < LEN(@s) + 2 AND SUBSTRING(',' + @s, N, 1) = ',';[/code]

RE: The Joy of Numbers

_ms65g_ — Sun, 23 May 2010 16:39:33 GMT

Thank you, But I do not mean publishing number table, I mean Splitting method is not simpler?Also, A simplified method for publishing number table (no loop, no recursion, and no ranking)[code="sql"];WITH C (i) AS(SELECT '0' UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5'UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9')SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4[/code]

RE: The Joy of Numbers

Jeff Moden — Sun, 23 May 2010 11:11:20 GMT

[quote][b]_ms65g_ (5/20/2010)[/b][hr]In splitting string using number table is not this approch simpler?[code="sql"]DECLARE @s VARCHAR(80)='Army,Navy,Air Force,Marines';WITH c AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM c WHERE n < 100)SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM c WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ','[/code][/quote]Sorry... got pulled away on other things...Yes... that method is much "simpler". It's also much slower in the grand scheme of things because recursive counters are as bad or worse than While Loops for performance and they use a lot more "reads" even if the reads are in memory.

RE: The Joy of Numbers

_ms65g_ — Thu, 20 May 2010 11:34:48 GMT

In splitting string using number table is not this approch simpler?[code="sql"]DECLARE @s VARCHAR(80)='Army,Navy,Air Force,Marines';WITH c AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM c WHERE n < 100)SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM c WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ','[/code]

RE: The Joy of Numbers

Jeff Moden — Sun, 16 May 2010 10:42:18 GMT

{edit} Still testing... comment removed. Sorry.

RE: The Joy of Numbers

_ms65g_ — Sun, 16 May 2010 00:11:58 GMT

Hi,At the part of eliminate duplicate what if we have a '!' character in string?So following solution is so simple and suitable for any scenario.And I use a new way for publishing numbers table.[code="sql"]DECLARE @s VARCHAR(500)='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaffffffffffffffffffffffffdddddddddddd ghqwer a d qqqq [pppp]'DECLARE @result VARCHAR(500)='';WITH c AS (SELECT 1 AS n UNION ALL SELECT 1 + n FROM c WHERE n < 100),c1 AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM c c1 CROSS JOIN c c2), k AS(SELECT n, k = SUBSTRING (@s, n, 1) FROM c1 WHERE n <= LEN(@s))SELECT @result = @result + kFROM k k1WHERE NOT EXISTS (SELECT * FROM k k2 WHERE k1.k = k2.k AND k1.n+1 = k2.n);SELECT @result AS removed;/*removed-----------------------afd ghqwer a d q [p][/code]

RE: The Joy of Numbers

Jeff Moden — Wed, 30 Dec 2009 00:15:52 GMT

[quote][b]james elmer (1/23/2007)[/b][hr]Good article. Interesting that my create table/insert while loop (to 10,000 rows) executed in 4 seconds and the set based solution executed in 17 seconds.[/quote]Heh... guess I've waited long enough... would you post your code, please?

RE: The Joy of Numbers

Jeff Moden — Sun, 23 Nov 2008 06:29:55 GMT

Ok... the Billion row results are in. On the orignal code that Russ submitted (looks like modification of Itzek's code), Insert Duration - 00:29:00Convert column to NOT NULL - 01:01:42Add Clustered PK - 01:27:59Total - 2:58:41First, we'll revert do Itzek's original rendition of the code with is about 5 minutes faster on my humble box... just notice the difference in placement of the ROW_NUMBER... [code];WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsNums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)SELECT N AS i INTO NUMBERS_TEST FROM Nums WHERE n <= 100000000[/code]Now, lemme show you one of those "undocumented" tricks that makes all the difference in the world... let's add a something that will make the "i" column NOT NULL as it's being built...[code];WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsNums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)SELECT [font="Arial Black"]ISNULL([/font]N[font="Arial Black"],0) [/font]AS i INTO NUMBERS_TEST FROM Nums WHERE n <= 100000000[/code]Believe it or not, it doesn't slow the code down at all and has the added benefit of making the result column NOT NULL. That wipes out the whole 01:01:42 for converting the column to NOT NULL in a separate step.Now, for the big suprise... after the table was built, the dedicated database I made for this test had expanded to 16.5 GB. Now, the database is set to the Simple Recovery Mode, and the "used" portion did drop back down to that vicinity, but the database MDF file expanded to over 30 GB to build the clustered index. In other words, adding the clustered index temporarily caused the disk usage to almost double for the sorts involved.I will say that I'm pretty happy that I don't have to create a Billion row numbers table everyday!

RE: The Joy of Numbers

Jeff Moden — Sat, 22 Nov 2008 09:17:36 GMT

Heh... dang it... I gotta remember... "Scroll Down". You have that in the code. :blush:I'm running some tests on this (cuz it's interesting)... I'll give a complete report when I'm done but, so far, the Insert completed on my humble 6 year old desktop (P4 1.8 Ghz, 1GB Ram, 5400 RPM Ide Drive, Windows XP, SQL Server 2005 sp2 Developer's Edition) in only 29 minutes and the conversion of the "i" column to INT NOT NULL (I didn't convert to BIGINT, just INT) only took an hour.

RE: The Joy of Numbers

Russ Bradberry — Fri, 21 Nov 2008 19:48:02 GMT

yeah, i added the clustered index after the insert to improve performance. the insert took just over an hour and a half but adding the clustered index took around 5 and a half hours. haha.

RE: The Joy of Numbers

Jeff Moden — Fri, 21 Nov 2008 19:24:41 GMT

Sounds like a lot of fun... thanks for the feedback. Did you index the numbers table with a clustered PK by any chance?

RE: The Joy of Numbers

Russ Bradberry — Fri, 21 Nov 2008 10:57:48 GMT

it was something of a math experiment. I was doing a specialized inner join on itself to find all prime numbers from 1 to a billion. its been going for about 3 days now. hahaha

RE: The Joy of Numbers

Jeff Moden — Wed, 19 Nov 2008 18:46:23 GMT

I've just gotta ask... what did you need the 1 Billion row numbers table for?

RE: The Joy of Numbers

Russ Bradberry — Wed, 19 Nov 2008 16:47:33 GMT

i had a need for a large number table, from 1 to 1 billion in just one table so neither of these solutions worked. i used cte to get what i wanted, i was able to do 1 million rows in under 6 seconds on a pentium d with 7200 rpm drives. where as the fastest solution proved thus far was about a minute and a half. 1 billion in an hour and a half awesome performance, i didnt have 3 days to wait for the others to finish. i thought id share my codeafter the "with" each line you add squares the previous number. so with this example we can put in just over 4 billion rows. the where clause limits the amount returned.after it is all inserted we add the pk and clustered indexes[code] SET NOCOUNT ON; DROP TABLE dbo.NUMBERS_TEST; WITH N5(i) AS ( SELECT 1 UNION SELECT 0 ), -- 2 N4(i) AS ( SELECT 1 FROM N5 D1 CROSS JOIN N5 D2 ), --4 N3(i) AS ( SELECT 1 FROM N4 D1 CROSS JOIN N4 D2 ), --16 N2(i) AS ( SELECT 1 FROM N3 D1 CROSS JOIN N3 D2 ), --256 N1(i) AS ( SELECT 1 FROM N2 D1 CROSS JOIN N2 D2 ), --65536 N0(i) AS ( SELECT 1 FROM N1 D1 CROSS JOIN N1 D2 ) --4294967296 SELECT i INTO NUMBERS_TEST FROM ( SELECT ROW_NUMBER() OVER (ORDER BY i) FROM N0) D(i) WHERE i <= 1000000000 ; --Put your limit hereGOALTER TABLE dbo.NUMBERS_TESTALTER COLUMN i BIGINT NOT NULL;GOALTER TABLE dbo.NUMBERS_TESTADD CONSTRAINT PK_NUMBERS_TEST PRIMARY KEY CLUSTERED (i);GO[/code]

RE: The Joy of Numbers

Wayne West — Tue, 22 Jan 2008 16:44:15 GMT

I'm developing a system for tracking how many days it takes to complete a task from when it's requested, but I have to exclude weekends and holidays. The original system was written in Access, and the contractor had a very clunky solution where he looped through a date table, touching every row (RBAR) to do this calculation. I realized that I, too, would need a date table, so I did one, and then came the problem of loading it. I originally did a loop to insert 104ish weekend dates, and then realized that I could do it with a number table! I was very happy, this was the first time that I got to use it. Here's the code:[font="Courier New"]CREATE TABLE [dbo].[DatesWeekendsHolidays] ( [WHDate] [smalldatetime] NOT NULL)GO--(The table Numbers contains a single smallint field, Number.)declare @ThisYear char(2)select @ThisYear = '08'-- DECLARE @StartDate smalldatetime-- DECLARE @EndDate smalldatetime-- DECLARE @LoopDate smalldatetime-- -- set @StartDate = cast(('1/1/' + @ThisYear) as smalldatetime)-- set @EndDate = cast(('12/31/' + @ThisYear) as smalldatetime)-- set @LoopDate = @StartDate--Using the Numbers table lets me insert all of the dates IN ONE INSERT STATEMENT!insert DatesWeekendsHolidays select dateadd(dw, Number, cast(('1/1/' + @ThisYear) as smalldatetime))from Numberswhere Number between 1 and 365 and (datepart(dw, dateadd(dw, Number, cast(('1/1/' + @ThisYear) as smalldatetime))) = 1 or datepart(dw, dateadd(dw, Number, cast(('1/1/' + @ThisYear) as smalldatetime))) = 7)--The loop method of inserting dates: 104 inserts.-- while @LoopDate <= @EndDate-- BEGIN-- set @dow = datepart(dw, @LoopDate)-- -- if @dow = 1 or @dow = 7-- insert DatesWeekendsHolidays select @LoopDate-- select @LoopDate = dateadd(d, 1, @LoopDate)-- END--New Year's Dayinsert DatesWeekendsHolidays select cast(('1/1/' + @ThisYear) as smalldatetime)where not exists(select WHDate from DatesWeekendsHolidays where WHDate = cast(('1/1/' + @ThisYear) as smalldatetime))--insert 11/11, 12/25 -- holidays with fixed dates--user inserts variable date holidays (Thanksgiving) via Access/VB app[/font]Ultimately I'll write a SQL job that will run at 00:01 on 1/1 of every year and automatically populate at least the raw holidays since it is easy enough to extract the year from getdate(). It would also send an email to the responsible parties to make sure that the variable date holidays get entered.The loop insert method took one second in Query Analyzer to run, the numbers insert took no measurable time. Looking at the statistics, trace, and execution plan are quite amusing, seeing the difference between the single insert statement and 104 is pretty funny.

RE: The Joy of Numbers

Jeff Moden — Fri, 27 Jul 2007 16:04:00 GMT

Cool... thanks, Michael.

Yeah, clearing the cashe didn't seem to matter in my testing so I didn't include it in my code. And, I very much like the idea of using your function to populate a temp table rather than a table variable. Thanks for running the test!

RE: The Joy of Numbers

Michael Valentine Jones — Fri, 27 Jul 2007 12:18:00 GMT

Jeff, I think your tests may have taken advantage of having master.dbo.syscolumns in cache, something that may or may not be the case. Also, one was loading a temp table with a SELECT INTO, and the other was doing an INSERT into a declared table.

I created a test script that clears the data cache and procedure buffers, and does a SELECT INTO for both methods.

My testing suggests a closer match in performance. The crosstab on master.dbo.syscolumns was faster than F_TABLE_NUMBER_RANGE in many tests, but not always, as you can see from the test below with 15,000,000 rows.

These tests were run on my SQL Server 2005 Developer Edition on my desktop PC.

--Removes all clean buffers from the buffer pooldbcc dropcleanbuffers--Removes all elements from the procedure cachedbcc freeproccachegoprint 'Test SysColumns crosstab'declare @starttime datetimeset @starttime = getdate()

set rowcount 15000000

select 	number = identity(int,1,1)into 	#tfrom	master.dbo.syscolumns sc1 with (nolock)	cross join	master.dbo.syscolumns sc2 with (nolock)

select Elapsed_Time_Cross = convert(char(13),getdate()-@starttime,114) godrop table #tgo--Removes all clean buffers from the buffer pooldbcc dropcleanbuffers--Removes all elements from the procedure cachedbcc freeproccachegoprint 'Test F_TABLE_NUMBER_RANGE'declare @starttime datetimeset @starttime = getdate()

select 	numberinto 	#tfrom 	dbo.F_TABLE_NUMBER_RANGE(1,15000000)

select Elapsed_Time_Function = convert(char(13),getdate()-@starttime,114)godrop table #t

DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Test SysColumns crosstab

(15000000 row(s) affected)

Elapsed_Time_Cross ------------------ 00:00:37:140

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Test F_TABLE_NUMBER_RANGE

(15000000 row(s) affected)

Elapsed_Time_Function --------------------- 00:00:36:940

(1 row(s) affected)

RE: The Joy of Numbers

Jeff Moden — Thu, 26 Jul 2007 19:11:00 GMT

Like Jackie Chan's Uncle would say "Oh! One more thing"... I keep a "Tally" table of 11,000 numbers as a permanent table... good for more than 30 years of dates by days (if you need that type of fuctionality) and certainly good enough for an 8k byte VARCHAR split function (if you need that). Here's how I build it... --===== Create and populate the Tally table on the flySELECT 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) --===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC

RE: The Joy of Numbers

Jeff Moden — Thu, 26 Jul 2007 19:06:00 GMT

Oh yeah... almost forgot... if you want a nice clustered primary key on both, the test and test results are as follows:

--===== Setup the test environment SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance & speed

--===== Declare local variablesDECLARE @DesiredRows INT --Number of rows desired in the result objectDECLARE @MyCount INT --Keeps track of row countsDECLARE @StartTime DATETIME --For calculation of duration times

--================================================================================--===== Loop through the tests from 10 to 10 million ============================= SET @DesiredRows = 10

WHILE @DesiredRows <= 10000000 BEGIN

--================================================================================-- Test the SELECT/INTO Method (temp table)--================================================================================--===== If the temporary Tally (Numbers) table exists, drop it -- (Included just for test repeatability. Should not have to do in real life -- because won't exist when new connection starts) IF OBJECT_ID('TempDB..#Tally','U') IS NOT NULL DROP TABLE #Tally

--===== Limit the desired number of rows SET ROWCOUNT @DesiredRows

--===== Start the timer and run the test SET @StartTime = GETDATE() SELECT IDENTITY(INT,1,1) AS N INTO #Tally FROM Master.dbo.SysColumns sc1 WITH (NOLOCK), Master.dbo.SysColumns sc2 WITH (NOLOCK) SET @MyCount = @@ROWCOUNT

--===== Add a primary key to the new table ALTER TABLE #Tally ADD PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Report the rowcount and duration in seconds PRINT STR(@MyCount) + ' Rows Inserted ' + CONVERT(CHAR(13),GETDATE()-@StartTime,114) + ' Duration SELECT/INTO Method'

--===== Return to normal unlimited rowcounts SET ROWCOUNT 0

--================================================================================-- Test the F_TABLE_NUMBER_RANGE Method (table variables)-- Function F_TABLE_NUMBER_RANGE available on this link -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 --================================================================================

--===== Start the timer and run the test SET @StartTime = GETDATE()DECLARE @N TABLE(Number INT PRIMARY KEY CLUSTERED WITH FILLFACTOR = 100) INSERT INTO @N SELECT Number FROM dbo.F_TABLE_NUMBER_RANGE(1,@DesiredRows)

--===== Report the rowcount and duration in seconds PRINT STR(@@ROWCOUNT) + ' Rows Inserted ' + CONVERT(CHAR(13),GETDATE()-@StartTime,114) + ' Duration F_TABLE_NUMBER_RANGE Method'

--===== Delete rows from the table variable so as not to build up rows between tests--TRUNCATE TABLE @N --Doesn't work on table variables...--DROP TABLE @N --Neither does this...DELETE @N --But this does.

PRINT REPLICATE('=',78)

10 Rows Inserted 00:00:00:110 Duration SELECT/INTO Method 10 Rows Inserted 00:00:00:000 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 100 Rows Inserted 00:00:00:110 Duration SELECT/INTO Method 100 Rows Inserted 00:00:00:000 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 1000 Rows Inserted 00:00:00:107 Duration SELECT/INTO Method 1000 Rows Inserted 00:00:00:017 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 10000 Rows Inserted 00:00:00:140 Duration SELECT/INTO Method 10000 Rows Inserted 00:00:00:093 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 100000 Rows Inserted 00:00:00:500 Duration SELECT/INTO Method 100000 Rows Inserted 00:00:00:907 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 1000000 Rows Inserted 00:00:04:063 Duration SELECT/INTO Method 1000000 Rows Inserted 00:00:09:907 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 10000000 Rows Inserted 00:00:51:033 Duration SELECT/INTO Method 10000000 Rows Inserted 00:01:59:123 Duration F_TABLE_NUMBER_RANGE Method==============================================================================

RE: The Joy of Numbers

Jeff Moden — Thu, 26 Jul 2007 18:46:00 GMT

If you always want the numbers table to start at 1, this demonstrates both an alternative to the function and a comparison of run times from 10 rows to 10 million rows...

--===== Setup the test environment SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance & speed

--===== Declare local variablesDECLARE @DesiredRows INT --Number of rows desired in the result objectDECLARE @StartTime DATETIME --For calculation of duration times

--================================================================================--===== Loop through the tests from 10 to 10 million ============================= SET @DesiredRows = 10

WHILE @DesiredRows <= 10000000 BEGIN

--===== Limit the desired number of rows SET ROWCOUNT @DesiredRows

--===== Start the timer and run the test SET @StartTime = GETDATE() SELECT IDENTITY(INT,1,1) AS N INTO #Tally FROM Master.dbo.SysColumns sc1 WITH (NOLOCK), Master.dbo.SysColumns sc2 WITH (NOLOCK)

--===== Report the rowcount and duration in seconds PRINT STR(@@ROWCOUNT) + ' Rows Inserted ' + CONVERT(CHAR(13),GETDATE()-@StartTime,114) + ' Duration SELECT/INTO Method'

--===== Return to normal unlimited rowcounts SET ROWCOUNT 0

--===== Start the timer and run the test SET @StartTime = GETDATE()DECLARE @N TABLE(Number INT) --Not sure why this works in a loop, but it does INSERT INTO @N SELECT Number FROM dbo.F_TABLE_NUMBER_RANGE(1,@DesiredRows)

--===== Report the rowcount and duration in seconds PRINT STR(@@ROWCOUNT) + ' Rows Inserted ' + CONVERT(CHAR(13),GETDATE()-@StartTime,114) + ' Duration F_TABLE_NUMBER_RANGE Method'

PRINT REPLICATE('=',78)

--================================================================================--===== End of test loop ========================================================= SET @DesiredRows = @DesiredRows * 10 END... and here's the results it produced on my humble 1.8 Ghz 1 GB Ram SQL Server 2000 SP 4 Developer's Edition desktop box at home...

10 Rows Inserted 00:00:00:000 Duration SELECT/INTO Method 10 Rows Inserted 00:00:00:000 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 100 Rows Inserted 00:00:00:000 Duration SELECT/INTO Method 100 Rows Inserted 00:00:00:013 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 1000 Rows Inserted 00:00:00:000 Duration SELECT/INTO Method 1000 Rows Inserted 00:00:00:017 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 10000 Rows Inserted 00:00:00:033 Duration SELECT/INTO Method 10000 Rows Inserted 00:00:00:077 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 100000 Rows Inserted 00:00:00:203 Duration SELECT/INTO Method 100000 Rows Inserted 00:00:00:750 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 1000000 Rows Inserted 00:00:02:000 Duration SELECT/INTO Method 1000000 Rows Inserted 00:00:08:093 Duration F_TABLE_NUMBER_RANGE Method============================================================================== 10000000 Rows Inserted 00:00:20:253 Duration SELECT/INTO Method 10000000 Rows Inserted 00:01:48:123 Duration F_TABLE_NUMBER_RANGE Method==============================================================================

RE: The Joy of Numbers

Michael Valentine Jones — Thu, 26 Jul 2007 14:13:00 GMT

I wrote the function in the code below to quickly generate number tables.

It executed this code to load a table with 1,000,000 numbers in 6.780 seconds. When I ran it to load a table with 10,000,000 numbers, it took about 136 seconds. 100,000 rows took 0.610 seconds, and 10,000 rows took 0.063 seconds.

declare @t datetimedeclare @n table( number int )set @t = getdate()

insert into @nselect 	numberfrom 	-- Function F_TABLE_NUMBER_RANGE available on this link 	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 	F_TABLE_NUMBER_RANGE(1,1000000)

select ElapsedTime = getdate()-@t

RE: The Joy of Numbers

Mike C — Sat, 10 Feb 2007 22:00:00 GMT

Hi James,Not sure why that would happen. I had the exact opposite experience on a few different SQL Server instances I tested on. There could be a lot of factors involved, however. One thing that comes to mind is if your SQL Server decided it needed to AutoGrow the database while creating the table, or some other oddity. If you could post the code you ran I'd love to test it out and see if I can reproduce it.Thanks!

RE: The Joy of Numbers

Jeff Moden — Tue, 23 Jan 2007 16:41:00 GMT

James,

Would you mind posting both versions? I'd like to do a little testing to see what's up... thanks.

RE: The Joy of Numbers

james elmer — Tue, 23 Jan 2007 11:19:00 GMT

Good article. Interesting that my create table/insert while loop (to 10,000 rows) executed in 4 seconds and the set based solution executed in 17 seconds.

RE: The Joy of Numbers

Mike C — Wed, 16 Aug 2006 08:02:00 GMT

Be careful with that:

SELECT @strReturn = COALESCE(@strReturn,'')+String+' ' FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ') WHERE Row >= 8

AFAIK, you can't rely on the results being returned in any particular order when doing a concantenation like that from a SELECT statement. You could conceivably end up with 'Lazy Dog Slow' or 'Dog Lazy Slow' or some other out-of-order result if SQL Server decided it was more efficient to deliver the results of the SELECT in a different order.

RE: The Joy of Numbers

Fal — Tue, 15 Aug 2006 17:33:00 GMT

Neat - I've not seen a select that way before. Closest I've come is something along the lines of "SELECT dbo.fn(...) FROM <table>"

Only change I'd make is initialize @strReturn first, lest you call COALESCE for every single row from the table when it only applies to the first row. (Although if NULL comes back then @strReturn will still be null - depends if that is a problem or not.) Something like:

DECLARE @strReturn VARCHAR(8000)
SET @strReturn = ''
SELECT @strReturn = @strReturn + String + ' ' FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ') WHERE Row >= 8
SELECT @strReturn

RE: The Joy of Numbers

David le Quesne — Tue, 15 Aug 2006 08:17:00 GMT

Thanks everyone, this has been really useful!

I have used the function to return all strings in the set after the nth string using a little feature for concatenating strings I found on this site...

eg:

DECLARE @strReturn VARCHAR(8000)

SELECT @strReturn = COALESCE(@strReturn,'')+String+' ' FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ') WHERE Row >= 8

SELECT @strReturn

this will return 'Slow Lazy Dog'

Cheers

David

RE: The Joy of Numbers

Mike C — Mon, 14 Aug 2006 22:19:00 GMT

Hi Simon,

Thanks for the catch. I don't know why I didn't test it with data with an empty first element in the list! (Doh!) Here's your solution converted over to take advantage of the SQL 2005 ROW_NUMBER() function and CTEs:

CREATE FUNCTION dbo.fnSetSplitSV (@String VARCHAR(MAX), @Delim VARCHAR(5))RETURNS TABLEASRETURN ( WITH Splitter (Num, String) AS ( SELECT Num, SUBSTRING(@String, CASE Num + DATALENGTH(@Delim) WHEN DATALENGTH(@Delim) THEN DATALENGTH(@Delim) ELSE Num + DATALENGTH(@Delim) END - DATALENGTH(@Delim), CASE CHARINDEX(@Delim, @String, Num) WHEN 0 THEN LEN(@String) - Num + DATALENGTH(@Delim) ELSE CHARINDEX(@Delim, @String, Num) - Num END ) AS String FROM dbo.Numbers WHERE Num <= LEN(@String) AND (SUBSTRING(@String, Num - DATALENGTH(@Delim), DATALENGTH(@Delim)) LIKE @Delim OR Num = 0) ) SELECT ROW_NUMBER() OVER (ORDER BY Num) AS Row, Num, String FROM Splitter)

RE: The Joy of Numbers

Steve Rosenbach — Mon, 14 Aug 2006 21:18:00 GMT

Thank you, Mike - this is one of the most useful and best-written articles I've ever seen on a practical SQL Server topic. I admit to being guilty of using a procedure method of doing the digest-the-delimited-list thing. As of tomorrow moring, I'm switching to your technique. I really like the idea of using auxilliary tables like this for set-based, rather than procedural data manipulation. I, too, have to credit Joe Celko with that idea - I read about calendar tables in one of his articles. In my current project, which involves writting stored procedures to feed Crystal Reports, I found that I could use a calendar table coupled with a cross-join to do in a fraction of a second what had been done earlier with time-consuming, loop-di-loop procedural code. I recommend that anyone who hasn't tried this powerful duo of auxilliary tables (numbers, calendar, etc) and cross-joins try it out Regards,SteveR

RE: The Joy of Numbers

Fal — Mon, 14 Aug 2006 21:00:00 GMT

What a useful trick, and definitely something I'll need to remember.

However, I think the function has a bug. When I put through either of the following...

SELECT * FROM dbo.fnSetSplit(',C,,,E')
SELECT * FROM dbo.fnSetSplit(',,C,,,E')

...I get odd results before 'C'. Instead, I knocked up the below with some adjustments:

i) the Numbers table starts from 0, not 1ii) accepts a variable delimiter, including one of variable length;iii) returns a row number so that you can choose the nth value.iv) use "like" for comparisons as SQL Server can do funny things comparing spaces

It does have a "quirk", however. If the delimiter is a multiple of the same character you can get odd results. Whether this is an error or not I think depends on the expected usage.

SELECT * FROM dbo.fnSetSplitSV(',,C,,,E',',') -- picks up the "missing" A
SELECT * FROM dbo.fnSetSplitSV(' TA fred TA mary TA albert',' TA ') -- variable delimiter
SELECT * FROM dbo.fnSetSplitSV(',,,C,,,E',',,') -- multiple delimiter with "quirk"

(Sorry if the below comes over with screwy formatting.)

CREATE FUNCTION [dbo].[fnSetSplitSV] (@String VARCHAR(8000), @Delim VARCHAR(5))

RETURNS @SplitTable TABLE

(

Row int identity

, Num int

, String varchar(8000)

)

BEGIN

DECLARE @DelimLen int

SELECT @DelimLen = datalength(@Delim)

INSERT INTO @SplitTable

SELECT

Num

, SUBSTRING(@String

, CASE Num + @DelimLen

WHEN @DelimLen THEN @DelimLen

ELSE Num + @DelimLen

END - @DelimLen

, CASE CHARINDEX(@Delim, @String, Num)

WHEN 0 THEN LEN(@String) - Num + @DelimLen

ELSE CHARINDEX(@Delim, @String, Num) - Num

END

) AS String

FROM dbo.Numbers

WHERE Num <= LEN(@String)

AND (SUBSTRING(@String, Num - @DelimLen, @DelimLen) like @Delim OR Num = 0)

RETURN

END

RE: The Joy of Numbers

Jeff Moden — Mon, 14 Aug 2006 20:56:00 GMT

Nicely done, Michael. Real good to see someone actually do a comparison in performance, too.Since you were kind enough to mention my name, I have to sing the praises of the guy that got me into using Tally/Numbers tables... Adam Machanic. Since that time, I've run into several DBA's that absolutely refused to allow a permanent Tally table (never mind a date table) to exist but would, strangly enough, allow a temp table... I needed a very high speed method to make one and, well, the rest is history.Great article and good references... wouldn't mind seeing you do a follow-up article on more Tally table techniques.

RE: The Joy of Numbers

B Bulhan — Mon, 14 Aug 2006 19:35:00 GMT

Thanks for the article. It certainly is useful.

RE: The Joy of Numbers

Mike C — Mon, 14 Aug 2006 14:40:00 GMT

Celko definitely knows what he's doing and he writes good books. I think people just get rubbed the wrong way by some of his responses on the newsgroups... but that's neither here nor there...

Aaron Bertrand has some excellent articles on using an Auxiliary Calendar Table over at ASPFAQ: http://www.aspfaq.com/show.asp?id=2519. I highly recommend using a date-based calendar table similar to the one he describes, because of the flexibility. You can use a calendar table like his to mark off regional holidays, easily calculate working days in a time period, etc. While you could probably do the same type of thing with a numbers table, it won't be as easy or as intuitive.

For Nigel: I prefer to use a single permanent numbers table. You eliminate the cost of re-creating it over and over (can become pretty substantial if you're re-creating it a lot and inserting a lot of numbers into it) and you can get a potential performance boost by using WITH SCHEMABINDING with UDF's that reference your permanent numbers table.

Thanks

RE: The Joy of Numbers

corey lawson — Mon, 14 Aug 2006 13:52:00 GMT

OK, I'll invoke the Calendar table that another poster mentioned. In other circles, this is attributed sometimes to Joe Celko, and a "Celko is an Idiot!" flamefest ensues... But in some ways, if you just have a numbers table, you can use date math to do it as well...but it really is probably the slickest ways to deal with date ranges (i.e., produce a result set that has the start date, end date, and all the dates between it) in SQL. Of course, it's easy enough to do it procedurally, but that kind of defeats the purpose of SQL in my book at least.

RE: The Joy of Numbers

nigelrivett — Mon, 14 Aug 2006 08:57:00 GMT

You might want to have a look at

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

Which shows how this sort of thing can be done in v2005.

Must admit I never create a permanent table like this but always create it on the fly - either as a derived table or table variable.

RE: The Joy of Numbers

YSLGuru — Mon, 14 Aug 2006 08:11:00 GMT

In addition to the benefits of a NUMBERS table shown by Micahel, the ability to intentionally generate a Cartisian product is anoter use of the table. I'm referring to a Cartesion product where you want X number of rows not to perform some function but to get X number of rows of data that would otherwise require X number of UNION statements. In our company we provide custom reporting for clients. On a few occassions I have seen some of our 'SQL Knowledge Limited' report writers create code that contains several queries UNION'd together because they need to generate a specific number of records from a query that would normally generate just 1 record. I don't recall the specifics, only that I improved the query's performance greatly by using a NUMBERS table approach to generate the X number of copies of a record that the prior UNION version was doing.

On a similiar note a DATE table is another wonderful utility table that works similiar to a NUMBERS table. I have a utility table I call DATELOOKUP that conatins 1 row for every date (MM/DD/YYYY) between a range of years that covers any time frame that a client would need to report on for now and for the next 10 years. Each row has many columns, each column containing a piece of info specific to the date. For example in our business it's important to use the first day of a month specified by the user. Without the date table, a combination of Date functions (like DateAdd) have to be used to get the first day of whatever MM/DD/YYYY a user enters as criteria. WIth the DATELOOK table I can join the data in DATELOOKUP to the date the user specifies and return the column from that record that contains the first day of said Month/Year. IN fact the code for generating and using the DATELOOKUP table is on the SQLServerCentral site within sample scripts.

Excellent article and information!

Thanks for sharing!