What is the purpose of system table master..spt_values and what are the meanings of its values?

Question

What is the purpose of system table master..spt_values?
Why was it provided and how one should use it?

What are the meanings of its type, low, high values?

Update:
Google search gives thousands of " its uses", for example:

• to split column using master..spt_values
• it contains numbers from 0 to 2047. It is very useful.for example if you need to populate a table with 100 numbers from this range
• for building indexes
• creating virtual calendars
• getting descriptions of used objects in somewhat non-intuitive and complicated way
  Enumerate all the indexes in a SQL Server database (Giuseppe Dimauro, devx.com)
• "SQL Server 2005 script to display disk space usage"
• "It's not worth talking about. This table is a design nightmare of a "super" lookup table"

Some posts warn against its use since it can be removed in future versions of SQL Server but there are already code scripts using it to illustrate new features of new SQL Server 11 (Denali):

• Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging

Answer 1

The spt_values table is not mentioned in the the SQL Server documentation but it goes back to the Sybase days and there is some extremely minimal documentation in the Sybase online docs that can be summed up in this comment:

To see how it is used, execute sp_helptext and look at the text for one of the system procedures that references it.

In other words, read the code and work it out for yourself.

If you poke around the system stored procedures and examine the table data itself, it's fairly clear that the table is used to translate codes into readable strings (among other things). Or as the Sybase documentation linked above puts it, "to convert internal system values [...] into human-readable format"

The table is also sometimes used in code snippets in MSDN blogs - but never in formal documentation - usually as a convenient source of a list of numbers. But as discussed elsewhere, creating your own source of numbers is a safer and more reliable solution than using an undocumented system table. There is even a Connect request to provide a 'proper', documented number table in SQL Server itself.

Anyway, the table is entirely undocumented so there is no significant value in knowing anything about it, at least from a practical standpoint: the next servicepack or upgrade might change it completely. Intellectual curiosity is something else, of course :-)

Answer 2

Mostly answered in another question.

What are the meanings of its type, low, high values?

Most Types (ie. each specific lookup table, if they were separate) require either Name or Number. Some Types require Low and High columns as well. In Sybase, we have an ErrorNumber column, so the sproc can RAISERROR which is not hard-coded (can be changes with versions, etc).

It is not "cryptic" unless an enumerated list is "cryptic" to you; it is just a lookup table (all of them, differentiated by the Type column).

Answer 3

One usage, definitely not its primary usage, is to generate a series of numbers:

--Generate a series from 1 to 100
SELECT
    TOP 100
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum
FROM master.dbo.spt_values ORDER BY RowNum