SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Joy of Numbers


The Joy of Numbers

Author
Message
Steve Rosenbach
Steve Rosenbach
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 206
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



Mike C
Mike C
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2535 Visits: 1168

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 TABLE
AS
RETURN

(
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
)


David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 32

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



If it ain't broke, don't fix it...
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1803

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

S.


Mike C
Mike C
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2535 Visits: 1168

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.


jelmer@minitab.com
jelmer@minitab.com
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 56
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84489 Visits: 41062

James,

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



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mike C
Mike C
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2535 Visits: 1168
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!
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5670 Visits: 11771

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 datetime
declare @n table( number int )
set @t = getdate()
insert into @n
select
number
from
-- 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

 

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84489 Visits: 41062

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 variables
DECLARE @DesiredRows INT --Number of rows desired in the result object
DECLARE @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)

--===== 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

--================================================================================
-- 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) --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'

--===== 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)

--================================================================================
--===== 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
==============================================================================



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search