How to create a minimal tally table and to describe its advantages (if any)

Question

How to create a minimal tally table and to describe its advantages (if any)

Viewing 15 posts - 31 through 45 (of 46 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Your efforts are much appreciated but the COUNT(*) stuff is going to confuse the very newbies that you're trying to not confuse (by using fnNumbers as the name).

I've also found that people won't even make a trip to SQLFiddle and they damned sure won't take the time to do a test nor even read multiple results across multiple files.

If you want a nice reference that's on StackOverflow where all the work has already been done, try pointing them to the following link on StackOverflow. I can vouch for the guy that wrote that answer. 😀

https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 2

Here's a set of comparisons between fnNumbers and fnTally

Performance comparison of fnNumbers vs fnTally inserting 10x row counts (n=1,000)

Performance comparison of fnNumbers vs fnTally inserting 10x row counts (n=10,000)

Performance comparison of fnNumbers vs fnTally inserting 20x row counts (n=1,000)

Performance comparison of fnNumbers vs fnTally inserting 40x row counts (n=1,000)

Performance comparison of fnNumbers vs fnTally inserting 100x row counts (n=1,000)

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

Any time I've needed to populate a table of sequential numbers, I just done it by brute force. No recursion or fancy stuff. Remember the definition of place valued numbers? Just implement that.

SELECT D1.unit + D2.tens + ...+ Dn.place_value

FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS D1 (unit)

CROSS JOIN

(VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (00)) AS D2 (ten)

...

Etc;

Please post DDL and follow ANSI/ISO standards when asking for help.

Steve Collins SSCrazy Points: 2160 More actions · Answer 4

jcelko212 32090 wrote:

Any time I've needed to populate a table of sequential numbers, I just done it by brute force. No recursion or fancy stuff. Remember the definition of place valued numbers? Just implement that.
SELECT D1.unit + D2.tens + ...+ Dn.place_value
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS D1 (unit)
CROSS JOIN
(VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (00)) AS D2 (ten)
...
Etc;

Would that work with a row goal? Are you suggesting to use a CTE pattern instead of itvf ? The purpose is to make it simple to use a tally sequence in ad hoc queries and to be ready with an explanation vs recursive cte's. If itvf is not practical (because too 'advanced' or can't be explained or ?) then a tested and efficient CTE pattern with an explanation vs rCTE's

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Steve Collins wrote:

jcelko212 32090 wrote:
Any time I've needed to populate a table of sequential numbers, I just done it by brute force. No recursion or fancy stuff. Remember the definition of place valued numbers? Just implement that.
SELECT D1.unit + D2.tens + ...+ Dn.place_value
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS D1 (unit)
CROSS JOIN
(VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (00)) AS D2 (ten)
...
Etc;
Would that work with a row goal? Are you suggesting to use a CTE pattern instead of itvf ? The purpose is to make it simple to use a tally sequence in ad hoc queries and to be ready with an explanation vs recursive cte's. If itvf is not practical (because too 'advanced' or can't be explained or ?) then a tested and efficient CTE pattern with an explanation vs rCTE's

I don't use that method but I don't believe a row goal will work there. TOP certainly wouldn't work as it's written.

If you want something really simple and inline for ad hoc queries, what would you want for the max number of rows (which is the max value of the values returned)? The answer can be "the normal 4.3 billion but thought I'd ask to be sure.

For totally ad hoc stuff that wouldn't go into production code, I use the CROSS JOIN of sys.allcolumns method. Short, sweet, and follows a row goal.

In the "old" days, I got along with using a physical Tally table of just 11,000 sequential values because it was larger than 8,000 for CHAR and VARCHAR but also contain slightly more rows than 30 years of dates (because I work with mortgages a fair bit).

Also, I really appreciate what you posted that you have on SQLFiddle but I think the use of COUNT() is going to confuse the very neophytes that you made the name change for. I also don't believe that many people will go and look at that many scripts and then look for multiple test results in each posting, never mind run the actual code to learn what they really need to learn whether they're on StackOverflow or not.

If you want to point folks to a single post that contains a single script and a single and obvious run result, have a look at the following link that's on StackOverflow. I happen to know that author. 😀

https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

>> Would that work with a row goal? Are you suggesting to use a CTE pattern instead of itvf? The purpose is to make it simple to use a tally sequence in ad hoc queries and to be ready with an explanation vs recursive cte's. <<

If you need a particular number of rows, then just add a " ..WHERE i BETWEEN <start> AND <final>" clause. That basic select statement. I showed you can be put in a CTE, a view or used as a standalone query as needed. We actually discussed whether or not to allow recursion in the SQL standards; many of us felt it was a bad idea because of the extra overhead that it entails. We lost the vote on the committee. I guess one of the problems is that the committee never met a feature didn't like 🙁

By the nature of the grammar, joins will always be faster and you really seldom implement something like Ackerman's function in SQL. The best way is just to create a permanent table of the size you need. Storage is cheap, joins are insanely fast, indexing makes it even faster,, etc.

As an aside, did you know that using the "fn-" prefix is from FORTRAN? And later in BASIC?

Please post DDL and follow ANSI/ISO standards when asking for help.

Steve Collins SSCrazy Points: 2160 More actions · Answer 7

Here's another one. It took 19 minutes vs 12 minutes for the rCTE. There's no sample data and no time for making any up. Hopefully the query is sound. It references the SO link. We'll see

https://stackoverflow.com/questions/64034753/the-maximum-recursion-100-has-been-exhausted-before-statement-completion-sql-se/64035031

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Steve Collins wrote:

Here's another one. It took 19 minutes vs 12 minutes for the rCTE. There's no sample data and no time for making any up. Hopefully the query is sound. It references the SO link. We'll see
https://stackoverflow.com/questions/64034753/the-maximum-recursion-100-has-been-exhausted-before-statement-completion-sql-se/64035031

Obviously you have some time because you posted some timing results. What are you using for parameter values??? 🙁

Can you post the test code you actually ran?

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 9

Jeff Moden wrote:

Steve Collins wrote:
Here's another one. It took 19 minutes vs 12 minutes for the rCTE. There's no sample data and no time for making any up. Hopefully the query is sound. It references the SO link. We'll see
https://stackoverflow.com/questions/64034753/the-maximum-recursion-100-has-been-exhausted-before-statement-completion-sql-se/64035031
Obviously you have some time because you posted some timing results. What are you using for parameter values??? 🙁

Ok I threw down an untested solution but it works perfectly. After the initial throw down (when my answer has already been beaten by 8 minutes) then I back test as best I can and make updates. Being first is important to maximize points. The OP provided no sample data so it's generally assumed they don't want a tested answer. Because if they did then they would've provided sample data.

While this has been happening I clocked another one.

https://stackoverflow.com/questions/64036103/how-to-create-a-generated-sequential-number-in-a-computed-value-in-a-select-stat/64036666

Here's a win from yesterday using DelimitedSplit8K

https://stackoverflow.com/questions/64015753/sql-server-how-to-query-within-a-string/64016735

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

jcelko212 32090 wrote:

I showed you can be put in a CTE, a view or used as a standalone query as needed.

Nope... you may have mentioned it but you've not "shown" anyone any code for those. You haven't even shown some code that actually works, Joe. Please post some code that works that demonstrates what you're talking about because I'm thinking that, after you fix it, the broken example will suffer from the WHERE clause just as Steve's WHERE clause did.

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Steve Collins wrote:

Jeff Moden wrote:
Steve Collins wrote:
Here's another one. It took 19 minutes vs 12 minutes for the rCTE. There's no sample data and no time for making any up. Hopefully the query is sound. It references the SO link. We'll see
https://stackoverflow.com/questions/64034753/the-maximum-recursion-100-has-been-exhausted-before-statement-completion-sql-se/64035031
Obviously you have some time because you posted some timing results. What are you using for parameter values??? 🙁
Ok I threw down an untested solution but it works perfectly. After the initial throw down (when my answer has already been beaten by 8 minutes) then I back test as best I can and make updates. Being first is important to maximize points. The OP provided no sample data so it's generally assumed they don't want a tested answer. Because if they did then they would've provided sample data.
While this has been happening I clocked another one.
https://stackoverflow.com/questions/64036103/how-to-create-a-generated-sequential-number-in-a-computed-value-in-a-select-stat/64036666
Here's a win from yesterday using DelimitedSplit8K
https://stackoverflow.com/questions/64015753/sql-server-how-to-query-within-a-string/64016735

[/quote]

Would you PLEASE post the actual test that you did on the first one we were talking about before moving on to another.

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 12

Jeff Moden wrote:

Would you PLEASE post the actual test that you did on the first one we were talking about before moving on to another.

Yes, sorry. This is the preferred answer. However, it appears the OP didn't go for it and just only wants his kludge to be made to work.

Function

CREATE FUNCTION COUNT_Dt(@d1 DATE, @d2 DATE, @nd VARCHAR(MAX))
RETURNS TABLE with schemabinding
AS
RETURN
select COUNT(*) SumOfDays 
FROM dbo.fnTally(0, datediff(d, @d1, @d2)) fn
        cross apply
        (select dateadd(d, fn.n, @d1) dt) da
WHERE EXISTS(SELECT 1
             FROM STRING_SPLIT(@nd,' ') 
             WHERE DATENAME(weekday,da.dt)=[value]);
go

Query

SELECT * FROM [dbo].[COUNT_dt]('2022-06-06','2029-08-08','Monday Tuesday')

Output

SumOfDays
750

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

The reason why I wanted you to post your test harness is because you claimed that the Tally solution took 19 MINUTES to the rCTE's 12 MINUTES on that post. There's something SERIOUSLY wrong with your machine if the results took MINUTES to run.

Using the following code...

SET STATISTICS TIME,IO ON;
SELECT * FROM [dbo].[COUNT]('2022-06-06','2029-08-08','Monday Tuesday')
OPTION (MAXRECURSION 0)  --Like I said... REQUIRED FOR YOU FUNCTION TO WORK!

SELECT * FROM [dbo].COUNT_Dt('2022-06-06','2029-08-08','Monday Tuesday')
;
SET STATISTICS TIME,IO OFF;

... I'm getting the following timing results where YOUR Tally function ate the rCTE solution for lunch on reads and was 4 times faster in MILLI-Seconds.

(1 row affected)
Table 'Worktable'. Scan count 2, logical reads 15727, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 26 ms.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 6 ms.

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 14

That's not how long the SQL code takes to execute. Tally based method would crush the OP's kludge especially as their test case is spanning thousands of days.

19 minutes is how long it took me to write the tally based code. 12 minutes is how long the first answer-er (GL) took. So when I added my answer there was already the other answer. It's annoying getting beaten to the first answer.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2160 More actions · Answer 15

Recall the hamburger analogy. If the OP has eaten McDonalds before my gourmet burger is ready then they won't be hungry when it arrives.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können