SQL Server help tables?

Question

If I write in SQL Server :

SELECT number FROM master..spt_values WHERE Type = 'P' ORDER BY Number

I get a list of sequential numbers which I can already use, instead of writing a table with sequential numbers.

This can be much helpful for small joins, row numbering etc.

I was wondering if there are other hidden tables like this one which can help me do other things as well? For example, I might want to select someNonImportantTextColmn from master..unknown and to apply a function of mine.

Where can I find a complete list of these "help tables"?

Answer 1

spt_values is not exactly a help table for your use. It is an undocumented system table used by internal stored procedures for a variety of tasks.

Since it contains neither unique nor contiguous numbers, and because it is undocumented and unsupported (and could simply vanish in a future version), I strongly recommend you don't use this table for anything.

You can always generate a sequence based on row_number from documented and supported system views, such as sys.all_objects, sys.all_columns, or a cross join of the two if you need a large number.

Or just build a Numbers table and store the numbers for yourself. This is likely to be more efficient in most cases because, if you use it enough, it will always be accessed in memory without having to calculate the sequence.