I was recently asked, “What is the DW.Numbers table used for in our database?”
The simple answer is that it is can be used to perform many tasks in a set based operation that would normally require a loop, or what Jeff Moden coined as RBAR (Row-By-Agonizing-Row).
There are many very helpful uses for the numbers table. Phil Factor and Robyn Page wrote a good workbench article up at Simple-Talk which includes many uses.
- Splitting Strings into table-rows, based on a specified delimiter
- Encoding and decoding a string
- Substituting values into a string
- Extracting individual words from a string into a table
- Extracting all the numbers in a string into a table
- Removing all text between delimiters
- Scrabble score
- Moving averages
- Getting the ‘Week beginning’ date in a table
- Calculating the number of working days between dates.
Splitting Strings is probably the most used function of the Numbers table. Maybe you noticed the APP.udf_SplitStringByDelimiter function in most of our tools.
Jeff Moden has a good write up of how the Numbers table is works to split the string and how it replaces a loop.
There are many interesting performance tests that have been ran, and some solutions are faster than others depending on the length of the string. If you are trying to get every last bit of performance check out these two articles by Aaron Bertrand. His first article compares the string split of the following techniques: SQL CLR, XML, Numbers table, and CTE. He revisited the technique earlier this year to test out SQL 2016’s SPLIT_STRING function.
As with anything performance related the environment has a lot to do with it. So while this guides are useful you would still need to test them on your production machine to determine the best solution for you.
There are many more articles on this which you can find by searching online for “SQL Numbers Table”, “SQL Tally table”, or “SQL Helper table”.
Are there things you have to do where you can see a use for the Numbers table?
Let us know in the comments below.
