SQL 系统表master..spt_values的目的是什么，其值的含义是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4273723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the purpose of system table master..spt_values and what are the meanings of its values?
提问by Gennady Vanin Геннадий Ванин
What is the purpose of system table master..spt_values?
Why was it provided and how one should use it?
系统表master..spt_values的目的是什么？
为什么提供它以及应该如何使用它？
What are the meanings of its type, low, high values?
它的类型、低值、高值的含义是什么？
Update:
Google search gives thousands of " its uses", for example:
更新：
Google 搜索提供了数千个“其用途”，例如：
- 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
- 使用 master..spt_values 拆分列
- 它包含从 0 到 2047 的数字。它非常有用。例如，如果您需要使用此范围内的 100 个数字填充表格
- 用于构建索引
- 创建虚拟日历
- 以某种非直观和复杂的方式获取所用对象的描述
枚举 SQL Server 数据库中的所有索引（Giuseppe Dimauro，devx.com） - 《显示磁盘空间使用情况的 SQL Server 2005 脚本》
一些帖子警告不要使用它，因为它可以在 SQL Server 的未来版本中删除，但已经有代码脚本使用它来说明新 SQL Server 11 (Denali) 的新功能：
- 在 SQL Server Denali 中使用 OFFSET N ROWS FETCH NEXT N ROWS ONLY 以便于分页
采纳答案by Pondlife
The spt_valuestable is not mentioned in the the SQL Server documentation but it goes back to the Sybase days and there is some extremely minimal documentationin the Sybase online docs that can be summed up in this comment:
spt_valuesSQL Server 文档中没有提到该表，但它可以追溯到 Sybase 时代，并且Sybase 在线文档中有一些非常少的文档，可以在此评论中总结：
To see how it is used, execute sp_helptext and look at the text for one of the system procedures that references it.
要了解如何使用它，请执行 sp_helptext 并查看引用它的系统过程之一的文本。
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"
如果您浏览系统存储过程并检查表数据本身，很明显该表用于将代码转换为可读字符串（除其他外）。或者正如上面链接的 Sybase 文档所说，“将内部系统值 [...] 转换为人类可读的格式”
The table is also sometimes usedin 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 requestto provide a 'proper', documented number table in SQL Server itself.
该表有时也用于MSDN 博客中的代码片段 - 但从未在正式文档中使用 - 通常作为数字列表的方便来源。但正如别处所讨论的，创建您自己的数字源是比使用未记录的系统表更安全、更可靠的解决方案。甚至有一个 Connect 请求在 SQL Server 本身中提供一个“正确的”、记录在案的数字表。
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 :-)
无论如何，该表完全没有记录，因此了解它没有任何意义，至少从实际的角度来看：下一个服务包或升级可能会完全改变它。当然，求知欲是另一回事:-)
回答by PerformanceDBA
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).
大多数类型（即每个特定的查找表，如果它们是分开的）需要名称或编号。某些类型也需要低和高列。在 Sybase 中，我们有一个 ErrorNumber 列，因此 sproc 可以是非硬编码的 RAISERROR（可以随版本等变化）。
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).
除非枚举列表对您来说是“神秘的”，否则它不是“神秘的”；它只是一个查找表（所有这些，由类型列区分）。
回答by Nicholas Humphrey
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
