Create a Tally Function (fnTally)

Question

Post reply

Create a Tally Function (fnTally)

Viewing 6 posts - 31 through 35 (of 35 total)

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

Aaron N. Cutshall SSCrazy Eights Points: 8904 More actions · Answer 1

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.

LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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

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

ratbak SSC Eights! Points: 939 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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://qa.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".

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 SSC Guru Points: 1000539 More actions · Answer 5

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.

Looking back at this, I finally found a decent use for DATEDIFF_BIG... it's to create UNIX TimeStamps of both the "seconds since 1970" and "milliseconds since 1970" flavor (the later of which is necessarily a BIGINT, which also fixes the "2038" issue.

As you say, it's absolutely fascinating to me that they didn't come out with a DATEADD_BIG but what do you expect from MS (see my previous rant, as well) when they do things like come out with an EOMONTH function but not an FOMONTH function?

And, yeah... glad they finally came out with a sequence generator and they finally fixed the huge oversight they made with String_Split(). I hope both are actually fast when 2022 RTM hits the streets. I'm generally not an early adopter of their stuff, especially after what I saw happen in virtually every release they've had since 2000 SP3 and so I've not even thought about any of the pre-release stuff they came out with... They don't pay me enough to be a member of the "QA" Team .

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

ratbak SSC Eights! Points: 939 More actions · Answer 6

ratbak

SSC Eights!

Points: 939

June 10, 2022 at 4:01 pm

#4047607

Looking back at this, I finally found a decent use for DATEDIFF_BIG... it's to create UNIX TimeStamps of both the "seconds since 1970" and "milliseconds since 1970" flavor (the later of which is necessarily a BIGINT, which also fixes the "2038" issue.

Yes, that's exactly my use case.