Databases
StackOverflow2013
Postsdbo
POWhat is the purpose of system table master..spt_values and what are the meanings of its values?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the purpose of system table master..spt_values and what are the meanings of its values?
    text
    copied!<p>What is the purpose of system table master..spt_values?<br> Why was it provided and how one should use it? </p> <p>What are the meanings of its type, low, high values? </p> <p>Update:<br> Google search gives thousands of " its uses", for example:</p> <ul> <li><a href="https://stackoverflow.com/questions/4273978/why-and-how-to-split-column-using-master-spt-values">to split column using master..spt_values</a></li> <li><a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102936" rel="noreferrer">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</a> </li> <li><a href="http://dbaspot.com/forums/sqlserver-server/237039-spt_values.html" rel="noreferrer">for building indexes</a> </li> <li><a href="http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html" rel="noreferrer">creating virtual calendars</a> </li> <li>getting descriptions of used objects in somewhat non-intuitive and complicated way<br> <a href="http://www.devx.com/vb2themax/Tip/18617" rel="noreferrer">Enumerate all the indexes in a SQL Server database (Giuseppe Dimauro, devx.com)</a> </li> <li><a href="http://www.dba-oracle.com/t_sql_server_2005_windows_script_display_disk_space_usage.htm" rel="noreferrer">"SQL Server 2005 script to display disk space usage"</a></li> <li><p><a href="http://www.sqlservercentral.com/Forums/Topic193376-19-1.aspx#bm193423" rel="noreferrer">"It's not worth talking about. This table is a design nightmare of a "super" lookup table"</a></p> <p>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): </p></li> <li><a href="http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/using-offset-n-rows-fetch-next-n-rows-on" rel="noreferrer">Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging</a></li> </ul>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload