INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Passing a list of values to a Stored Procedure (Part III) by ESquared
Posted: 19 Jul 07

First, see Passing a list of values to a Stored Procedure (Part II). I am adding part 3 to give you more options, especially to handle

  • Cases where the input variable is of text datatype and perhaps extremely long
  • Cases where the input tokens are of known length or of a relatively low maximum length
  • The use of a numbers table instead of a loop


  • So here you go.

    First, create a Numbers table

    If you don't have a Numbers table, here's some code for you to create one. Note that there are debates about the use of this technique. In many situations, using a Numbers table can be a sign of sloppinessùbut not always. See option 6 for some notes about improved performance. Note that the clustered primary key in this table is absolutely essential to its proper performance. I pick 8000 numbers for arbitrary reasons, to have a good mix of small size (speed) and range of numbers (too small and I can't do the work I need to do).

    CODE

    CREATE TABLE Numbers (Num int identity(1, 1) NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED)
    SET NOCOUNT ON
    INSERT Numbers DEFAULT VALUES
    WHILE Scope_Identity() < 8000 INSERT Numbers DEFAULT VALUES
  • Option 5 - use a Numbers table


  • Now that you have a Numbers table, here's a function that uses it to split a string into columns.

    CODE

    CREATE FUNCTION SplitNumbers (
       @InputText varchar(8000),
       @Delimiter varchar(1)
    )
    RETURNS TABLE
    AS
    RETURN
    (
       SELECT
          --This first column will drastically hurt performance
          TokenID = Num - Datalength(Replace(Left(@InputText, Num - 1), @Delimiter, '')),
          Value = Substring(@InputText, Num, CharIndex(@Delimiter, @InputText + @Delimiter, Num) - Num)
       FROM Numbers
       WHERE
          Substring(@Delimiter + @InputText, Num, 1) = @Delimiter
          AND Num <= Datalength(@InputText) + 1
    )
    If you compare this to option 4 from the previous FAQ, you'll find that it's much faster for longer strings (some testing is in order for short strings).

    But after a lot of research on this topic more than two years ago, I found an article that tested performance of all the various ways of splitting a string into rows. (Yes, it took me long enough to come write this FAQ. I'll try to find that article and if it's still out there will provide a link.) The clear winner was to use a Numbers table against fixed-length inputs. The idea is instead of submitting a string '1,2,7,42' with delimiters between the values, the client application submits them with padding so they are all the same width: for example '1  2  7  42 ' could be an input string with a token length of 3 characters for each number.

    One problem with the numbers table though is, what if you want to use a text variable with extremely long lists of numbers? Let's say you're using positive integers, which in SQL Server can be ten characters long. In an 8000-character string, that's only 800 values. It's not a stretch to imagine a client needing to work with 800 rows at once, for example an orders table could easily have this many for just one day.

    So combining the fixed-length technique with a cross-join of the numbers table against itself (to handle up to 64,000,000 characters in the case of an 8000 number table), here is

  • Option 6 - Numbers table with fixed-length tokens


  • So here is the culmination of my search for the absolutely fastest split function in SQL Server that can handle very long strings. Make sure you have a Numbers table (see the code above if you don't). It can have any number of numbers in it, but note that if you have n numbers then the maximum length of a string you can handle is n2.

    CODE

    CREATE FUNCTION SplitFixed (
       @InputText text,
       @TokenLength tinyint
    )
    RETURNS TABLE
    AS
    RETURN
    (
       SELECT
       TokenID = N1.Num + MaxNum * (N2.Num - 1),
       Value =
             SubString(
                @InputText,
                @TokenLength * (N1.Num + MaxNum * (N2.Num - 1) - 1) + 1,
                @TokenLength
             )
       FROM
          Numbers N1
          CROSS JOIN (
             SELECT MaxNum = Max(Num) FROM Numbers
          ) M
          JOIN Numbers N2 ON
             @TokenLength * (M.MaxNum * (N2.Num - 1) + N1.Num - 1) + 1 <= DataLength(@InputText)
       WHERE
          N2.Num <= DataLength(@InputText) / (MaxNum * @TokenLength) + 1
          AND N1.Num <=
             CASE
             WHEN DataLength(@InputText) / @TokenLength <= MaxNum
                THEN DataLength(@InputText) / @TokenLength
                   + CASE DataLength(@InputText) % @TokenLength
                   WHEN 0 THEN 0
                   ELSE 1
                   END
             ELSE MaxNum
          END
       )

    Back to Microsoft SQL Server: Programming FAQ Index
    Back to Microsoft SQL Server: Programming Forum

    My Archive

    Resources

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close