#2 SQL Tips - A Numbers table (Part 1)
The longer I’m a SQL guy, the more convinced I am that many of the worlds problems can be solved with a numbers table. Well, SQL problems anyway. A numbers table is really nothing more than an ordered table of sequential integers from 0 or 1 to some useful though not huge number. Maybe 8000 or 1Million, maybe more depending on your uses. This table will serve as a counter just like looping through a WHILE loop or incrementing some counter variable or identity column, etc. The difference between those counters and a numbers table is that the numbers table is pre-generated and because SQL was born to work with set-based/table-based data really efficiently (vs generating counters and looping and doing string manipulation like true CLR/Programming languages do), we are able to realize incredible speed gains when we leverage that fact as much as possible.
So, you may say to me “OK, John…now I’ve got a big ass list of numbers...um…so now what?”
Well, the thinking goes like this:
Do I need to parse a string?
Do I need to remove dupes from a string?
Do I need to extract certain items from a string?
Do I need to generate a range of dates?
Do I need to generate a range of times?
Do I need to generate a list of IP addresses?
Do I need to find gaps in identity columns?
Do I need to do anything that steps or loops or adds or otherwise moves sequentially forward or backward through anything?
If you’ve said “yes” to any of those, then you maybe should create a numbers table on any and all of your SQL servers. Now, of course I understand that it’s fairly easy to generate a sequential list of numbers at runtime in a CTE or otherwise, but in many (most?) cases a static numbers table will perform better because the table is likely already in memory and ordered/indexed and there’s no need to add the processing and memory requirements of number generation—light as they may be—to runtime.
Read more: #2 SQL Tips - A Numbers table (Part 1)
- Created on .
