The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Question

Post reply

The "Numbers" or "Tally" Table: What it is and how it replaces a loop

August 8, 2022 at 9:09 am

This was removed by the editor as SPAM

Viewing 15 posts - 496 through 510 (of 511 total)

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

Fantastic article, do you have any links to using the % to return value. never seen the use of % like this (T.N-1)%3 +1

thanks

***The first step is always the hardest *******

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

SGT_squeequal wrote:

Fantastic article, do you have any links to using the % to return value. never seen the use of % like this (T.N-1)%3 +1
thanks

The % is the modulus operator, returns the remainder of the division of the value by the second parameter.

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

SGT_squeequal wrote:

Fantastic article, do you have any links to using the % to return value. never seen the use of % like this (T.N-1)%3 +1

Thank you for the feedback... much appreciated.

As Eirikur states, the "%" (in this case) is the arithmetic "modulo" operator. In SQL Server, that means the straight remainder of division (and it works equally well with integers and real numbers, both positive and negative). It's not quite the same as the modulo operator that EXCEL and the Scientific Calculator in Windows use because those use the "FLOOR" method for calculating Modulo rather than the TRUNCATE method that SQL Server (and most humans) use.

For example, -5%3 will return a "-2" and MOD(-5,3) in Excel will return a "1". Both answers are technically and mathematically correct. You just have to know which method of calculation is being used behind the scenes. To wit, a whole lot of programming languages use the more human natural truncate method (also known as the "Remainder" or "Remainder Method") while others use the not so human natural floor method (described as the"least positive residue" or "modular arithmetic" method in which remainders are only positive) and the documentation will usually NOT tell you which one is being used. The -5 MOD 3 test is a good test for this.

Here's a link for the "% (modulo)" arithmetic operator in SQL Server

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/modulo-transact-sql?view=sql-server-ver15

WikiPedia has a good introduction to the variants of the operation as well as links to other articles on the subject.

https://en.wikipedia.org/wiki/Modulo_operation

Our human base 10 numbering system is based on "Mod 10"... it only has digits for 0 to 9.

Also, there's a functional substitution for the Tally Table. One example of that can be found at the "fnTally" link in my signature line below.

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

Stelios Newbie Points: 3 More actions · Answer 4

Thank you for all this.

Despite the knowledge I gained, the beauty of learning more effective ways is what keeps me going on.

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

Stelios wrote:

Thank you for all this.
Despite the knowledge I gained, the beauty of learning more effective ways is what keeps me going on.

I definitely agree with that. Thanks for stopping by.

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

david.wright-948385 SSCarpal Tunnel Points: 4041 More actions · Answer 6

Good timing - the current feature list for SQL Server 2022 includes the function GENERATE_SERIES(), which looks like a handy way to generate the Numbers or Tally rows.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

david.wright-948385 wrote:

Good timing - the current feature list for SQL Server 2022 includes the function GENERATE_SERIES(), which looks like a handy way to generate the Numbers or Tally rows.

Agreed. It took them long enough. IIRC, Erland Sommarskog submitted his first request on the old "Connect" site back in 2008. The replies from MS suggested that they didn't even know what someone would use it for. I and a few others added a whole lot of things we use it for and included the fact that other relational database engines contained such a thing.

I don't have the time to "play" with pre-releases but I've been keeping tabs on what some of the big guns have been saying about it (particularlly, Erik Darling because he's been analyzing it at the Execution Plan level). It sounds like they did well for performance. I can't wait for 2022 RTM to actually hit the streets and for the Developers Edition to come out. That's when my fun will start. 😉 .

I'm also tickled that they finally fixed the String_Split() function. Again, we shall see what we see when it finally hit's the streets.

Thanks for the feedback on this now 14 year old article. It's coming up on almost 140 thousand reads. It did its job although it still explains a lot more than just how to produce a series of numbers.

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

Nate Schmidt SSC Veteran Points: 288 More actions · Answer 8

In the first paragraph (of the 5-Sep-2022 reposting) in the Editor's Note that there is new updated code for the Splitter Functions and then a "The New Splitter Functions" link for the reader to get that code, clicking on the link produces a "page not found" error for me (in Chrome). Hovering over the link it looks like it is connected to a zip file, maybe? I'm thinking something needs correcting somehow - and THANKS! in advance.

Nate

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Nate Schmidt wrote:

In the first paragraph (of the 5-Sep-2022 reposting) in the Editor's Note that there is new updated code for the Splitter Functions and then a "The New Splitter Functions" link for the reader to get that code, clicking on the link produces a "page not found" error for me (in Chrome). Hovering over the link it looks like it is connected to a zip file, maybe? I'm thinking something needs correcting somehow - and THANKS! in advance.
Nate

That, apparently, happened during the last move (to WordPress). The first link does work and, if you scroll to the bottom of that article, there's a "Resources" section with the ZIP files the 2nd link was supposed to take you to.

Many people have contributed to the code, in one form or another. The most recent and significant contribution was provided by Eirikur Eiriksson at a seemingly unrelated link ( https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 ) from a little after 2012 where he uses LEAD to basically double the performance, yet again. Although I've kept the old name the same (DelimitedSplit8k), I have added his method (and credits) to my personal copy of that function.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Jeff Moden wrote:

david.wright-948385 wrote:
Good timing - the current feature list for SQL Server 2022 includes the function GENERATE_SERIES(), which looks like a handy way to generate the Numbers or Tally rows.
Agreed. It took them long enough. IIRC, Erland Sommarskog submitted his first request on the old "Connect" site back in 2008. The replies from MS suggested that they didn't even know what someone would use it for. I and a few others added a whole lot of things we use it for and included the fact that other relational database engines contained such a thing.
I don't have the time to "play" with pre-releases but I've been keeping tabs on what some of the big guns have been saying about it (particularlly, Erik Darling because he's been analyzing it at the Execution Plan level). It sounds like they did well for performance. I can't wait for 2022 RTM to actually hit the streets and for the Developers Edition to come out. That's when my fun will start. 😉 .
I'm also tickled that they finally fixed the String_Split() function. Again, we shall see what we see when it finally hit's the streets.
Thanks for the feedback on this now 14 year old article. It's coming up on almost 140 thousand reads. It did its job although it still explains a lot more than just how to produce a series of numbers.

Have some patience, Jeff. Do you really think 16 years is a long time to add a function to a product where it should have been there in the first place? 😀 Make no mistake, I will performance test it when I get access to a SQL 2022 instance. To me, it's not worth installing Dev Edition on my own laptop. It runs fine just the way it is.

In fact, I clearly remember having lunch and a toast with you to celebrate the 10-year anniversary of Erland's MS Connect item. It was still open and before they migrated to their newest system.

This reply was modified 2 years, 3 months ago by Ed Wagner.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

AnthonyR SSC-Addicted Points: 453 More actions · Answer 11

Ed Wagner wrote:

Make no mistake, I will performance test it when I get access to a SQL 2022 instance. To me, it's not worth installing Dev Edition on my own laptop. It runs fine just the way it is.

Have you ever looked into Docker containers? Makes it very easy to spin up specific patch levels of SQL Server 2017, 2019 and 2022 for regression testing, etc..

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 12

AnthonyR wrote:

Ed Wagner wrote:
Make no mistake, I will performance test it when I get access to a SQL 2022 instance. To me, it's not worth installing Dev Edition on my own laptop. It runs fine just the way it is.
Have you ever looked into Docker containers? Makes it very easy to spin up specific patch levels of SQL Server 2017, 2019 and 2022 for regression testing, etc..

IMHO, containers are fine for certain levels of regression testing, but cannot substitute proper like-for-like testing.

😎

Several times, the containers performed perfectly when production fell flat on its face for various reasons such as storage, memory or communication latency 😉

YSLGuru SSC-Insane Points: 21661 More actions · Answer 13

NOTE: I was not the one who designed the DB I am working with so I can not ask questions as to "why did they do that" or 'Why did they design it like that". I am merely forced to work with what is.

Is there such a thing as too large a Tally or Numbers table? Most examples I have seen on creating and using a Number or Tally tally table is between the thousand and million mark but what about a NUMBERS tally table that goes from 1 to 1 Billion or 10 billion, is that to high?

I ask because was interested in using a NUMBERS tally table to locate missing values in a table where the value can be anywhere from 1 to 999,999,999,999,999,999 . The column in question is of type NUMERIC(18,0) and while no row has gotten up to 18 digits in length it is possible. Currently the highest number has only reached the 10th digit so at current I was thinking of creating a numbers tally table with rows ranging from 1 to 9999999999 and using it to help locate missing values. Is that still an appropriate use for a Numbers tally table or when you get to that high a number you should be using other means instead of a numbers tally table?

I've been using a Date tally table effectively for over a decade and it has proved invaluable but even with decades of dates stored it pales in comaprison to the number of rows I'm looking at for a Numbers tally table.

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

IIRC, you'll find no place where I've suggested doing such a thing in the article because there are much more effective methods to find gaps in a sequence of values. Joining two billion row tables to do such a thing just makes no sense. In truth, it doesn't even make sense for tables as small as just 1,000 rows.

There are two very efficient alternatives that I know of... One, of course, by Itzik Ben-Gan and one that I've posted that I got from someone a very, very long time ago. All I did was a tweak here and there of that code. You can find both methods at the following article where Wayne Sheffield compares the performance of several methods.

https://blog.waynesheffield.com/wayne/archive/2012/04/sql-server-2012-performance-test-gap-detection/

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