First Tally Table T-SQL

  • Agreed. One other important caveat - anyone running 32-bit SQL Server (why?) should go with a Tally solution too. SQLCLR and 32-bit servers are not a good fit - in my opinion.

  • For sure nothing will touch the performance of C#/C++ for that type of function.

    For use on a table, you would probably need a scalar function, and those don't perform that well anyway.

    Fwiw, here's my take on it, using just T-SQL. My upper-casing rule is (1) the first character and (2) any other letter preceded by a character that is NOT a letter:

    CREATE FUNCTION dbo.MixedCase (

    @string varchar(8000)

    )

    RETURNS varchar(8000)

    AS

    BEGIN

    DECLARE @byte int

    DECLARE @prevByte int

    SET @string = UPPER(LEFT(@string, 1)) + LOWER(SUBSTRING(@string, 2, 8000))

    SET @prevByte = 0

    SET @byte = PATINDEX('%[^a-z][a-z]%', @string)

    WHILE @byte > 0

    BEGIN

    SET @string = STUFF(@string, @prevByte + @byte + 1, 1,

    UPPER(SUBSTRING(@string, @prevByte + @byte + 1, 1)))

    SET @prevByte = @prevByte + @byte

    SET @byte = PATINDEX('%[^a-z][a-z]%', SUBSTRING(@string, @prevByte + 1, 8000))

    END --WHILE

    RETURN @string

    END --FUNCTION

    SELECT dbo.MixedCase(data)

    FROM temp

    Scott Pletcher, SQL Server MVP 2008-2010

  • scott.pletcher (6/29/2010)


    Fwiw, here's my take on it, using just T-SQL. My upper-casing rule is (1) the first character and (2) any other letter preceded by a character that is NOT a letter:

    I agree... those two rules are all that's needed and no special handling for rule 1 is necessary. As your code indicates, just do it.

    You can certainly convert your code to an implementation of a Tally Table or Tally CTE so that you could convert the function to an iTVF for a huge performance improvement over a scalar function. In this case, it's not the While Loop that would be such a drag (mostly memory only code)... it's the fact that a While Loop would only work in a scalar or mlTVF (I know you know that... just saying it outloud for others that may read this).

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Paul White NZ (6/29/2010)


    Agreed. One other important caveat - anyone running 32-bit SQL Server (why?) should go with a Tally solution too. SQLCLR and 32-bit servers are not a good fit - in my opinion.

    Heh... yeah... my trusty 9 year old desktop at home is still 32 bit. "It takes a lickin' and keeps on tickin'!":-P It also helps me when I have the need to build something with blinding speed for work... if I can make it fly on my machine, on my ol' 32 bit, 1.8Ghz, single P4, just imagine what it does on a 4x64 or better. :w00t:

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (6/29/2010)


    Heh... yeah... my trusty 9 year old desktop at home is still 32 bit. "It takes a lickin' and keeps on tickin'!":-P It also helps me when I have the need to build something with blinding speed for work... if I can make it fly on my machine, on my ol' 32 bit, 1.8Ghz, single P4, just imagine what it does on a 4x64 or better. :w00t:

    Yep I too only have 32-bit SQL Server at home, although I have the awesome power of a 2GHz single-core *mobile* P4 at my disposal :laugh:

  • scott.pletcher (6/29/2010)


    For sure nothing will touch the performance of C#/C++ for that type of function.

    Any CLI language will do...let's not forget the VB guys out there (Barry) or those that prefer F# or IronRuby...http://en.wikipedia.org/wiki/List_of_CLI_languages

    For use on a table, you would probably need a scalar function, and those don't perform that well anyway.

    APPLY makes using an in-line TVF a theoretical possibility. CLR scalar functions perform much better than T-SQL scalar functions.

    Fwiw, here's my take on it, using just T-SQL.

    Have you tested that for performance against the other methods presented previously?

    Don't forget the value of schema binding your scalar functions.

    Paul

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply