Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.