1

I was poking around a TFS database today to try and run some statistics and I came across a table called tbl_Number. This table contains one column Number, and all the values are just the values 1 to 500,000. None of the values differ from their respective index in the list, as you can see in the screenshot from queries I ran in LinqPad:

enter image description here

Tbl_Numbers.Max(x => x.Number).Dump(); //max value
Tbl_Numbers.Count().Dump(); //number of entries
var asList = Tbl_Numbers.ToList();
asList.Where(x => asList[x.Number - 1].Number != x.Number).Any().Dump();
//False shows that every entry matches the value at its ordinal location in the list

My question is: What would the use of such a table be? Is this in case one of the referenced numbers needs to change for some reason? The only way to identify a number from this table is by using that same number, so I don't see what use this table could be.

I realize this question could lead to answers that are conjecture, but I'd be interested to see if there's some programming principal that I'm unaware of that's being used here.

DLeh
  • 23,806
  • 16
  • 84
  • 128
  • maybe interesting? http://stackoverflow.com/a/29969124/3184785. Useful in 'cross joins' for generation of a list of 'anything that can be calculated from the integer position in the list'. – Ryan Vincent May 02 '16 at 14:40
  • @RyanVincent ah i see now how that could be useful in writing sql statements, thanks for the link! – DLeh May 02 '16 at 14:43
  • Without being the author of the database or the table we cannot possibly give you the reasons why this was done. – JonH May 02 '16 at 14:43
  • 1
    Check out this question and its links, comment and answers... http://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable – Isaac May 02 '16 at 14:44
  • 2
    may be you are looking at :http://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable – TheGameiswar May 02 '16 at 14:44
  • 1
    Those are referred to as "Tally" tables. They allow you to have pregenerated series of something, so you can generate new series. ie: You could "select dateadd(d, number-1, getdate()) from tallyTable where number <= 100" to get a series of next 100 days. There are many similar uses. Nowadays, you can generate such series on the fly (ie: with MS SQL you could "SELECT TOP 500000 ROW_NUMBER() OVER ( ORDER BY t1.Object_ID ) AS Number FROM master.sys.all_columns t1 CROSS JOIN master.sys.all_columns t2;") and some good databases even have a built-in method to do so. – Cetin Basoz May 02 '16 at 14:56

1 Answers1

3

It can be used in OUTER JOINS to make sure that you always get all the numbers in a given range, even if there is no data related to that number.

For example, suppose I want to return the count of customers who bought 3,4 or 5 products on their last order. But in fact, there are no customers who bought 4 products. If I just ran a count query on my data, I wouldn't get a row for the customers who bought 4 products at all.

However, If I query my numbers table and LEFT JOIN to my data, I will get the number 4, and a count of 0 or NULL, depending on how I wrote my query.

People also often do this with Date tables, by the way.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52