Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    I also consider that to be mostly a "marketing for Microsoft" site.  Consider the number of hits the bloody community put on the "dark theme" request in SSMS and how few hits there are on the request to fix the broken STRING_SPLIT() function or the 12 years it's been for a high performance machine language level fnTally() function to be built into T-SQL like it has been in much cheaper versions of RDBMS software almost since their inception 1 to 2 decades ago.

    And then there are "joys" like the bloody performance issues built into the FORMAT() function and the fact that the newer temporal datatypes like DATE, TIME, DATETIME2 are no longer ANSI compliant when it comes to date math.

    None of that will ever be fixed because it's obviously much more important to come of with things like the "dark theme" than it is to fix partially broken or limited usage stuff.  Heh... and when they do, it's not actually a fix but some bloody workaround like DATEDIFF_BIG or the current rendition of that damned PIVOT operator (which is MUCH better in MS Access!).

    It's OK, Jeff! Don't hold back! Tell Microsoft how you really feel! 😀

    I also would love to see "a high performance machine language level fnTally() function to be built into T-SQL like it has been in much cheaper versions of RDBMS software almost since their inception 1 to 2 decades ago." Postgres has a pretty nifty (and fast!!) generate_series() function for multiple data types.

  • The Postgres function is the one that I remember the best.  I've not tested it for performance because I've never had to work on a Postgres DB.  Oddly enough, I have been able to help some folks out in Postgres just by reading the documentation on the language (there are quite a few differences in things like the temporal functions).  I've never actually written even a single line of code in it myself. 😀

    Heh... as for holding back... I actually AM holding back!  😀

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • I considered submitting a request asking for a way to suppress the dark mode request.

    I actually have to use DATEDIFF_BIG.  The fact they couldn't be bothered to implement the corresponding DATEADD_BIG is frustrating.  I wonder why they couldn't have just implemented implicit type conversion to bigint in the existing functions.

     

  • You can ab

    ratbak wrote:

    I considered submitting a request asking for a way to suppress the dark mode request.

    I actually have to use DATEDIFF_BIG.  The fact they couldn't be bothered to implement the corresponding DATEADD_BIG is frustrating.  I wonder why they couldn't have just implemented implicit type conversion to bigint in the existing functions.

    You can easily avoid the need for DATEDIFF_BIG.  The following article shows how to do that, which also demonstrates the inadequacies of the newer temporal datatypes.  MS did us no favors there.

    https://www.sqlservercentral.com/articles/calculating-duration-using-datetime-start-and-end-dates-sql-spackle-2

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 4 posts - 31 through 34 (of 34 total)

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