Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The Joy of Numbers Expand / Collapse
Author
Message
Posted Monday, August 14, 2006 9:18 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:00 PM
Points: 91, Visits: 195
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



Post #301770
Posted Monday, August 14, 2006 10:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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
)

Post #301775
Posted Tuesday, August 15, 2006 8:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, 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...
Post #301852
Posted Tuesday, August 15, 2006 5:33 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 505, Visits: 1,691

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.

 

Post #302005
Posted Wednesday, August 16, 2006 8:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #302104
Posted Tuesday, January 23, 2007 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:19 AM
Points: 42, Visits: 51
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.
Post #339093
Posted Tuesday, January 23, 2007 4:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 35,262, Visits: 31,747

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #339205
Posted Saturday, February 10, 2007 10:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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!
Post #344036
Posted Thursday, July 26, 2007 2:13 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502

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

 
 
Post #385579
Posted Thursday, July 26, 2007 6:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 35,262, Visits: 31,747

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #385635
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse