系统表master..spt_values的目的是什么，它的值的含义是什么？ [英] What is the purpose of system table master..spt_values and what are the meanings of its values?

问题描述

系统表master..spt_values的目的是什么？

为什么它提供了，应该如何使用它？

其类型，低值，高值的含义是什么？

更新：

Google搜索提供数千种其用途，例如：

• 拆分列使用master..spt_values


• 它包含从0到2047的数字。这是非常有用的，例如，如果你需要填充一个表，从这个范围的100个数字


• 用于构建索引


• 创建虚拟日历


• 以非常直观和复杂的方式获取用过的对象的描述
  
  枚举SQL Server数据库中的所有索引（Giuseppe Dimauro，devx.com）


• SQL Server 2005脚本以显示磁盘空间使用情况



• 这不值得一提。该表是超级查找表的设计噩梦

  
  
  

  某些帖子警告其使用，因为它可以在将来版本的SQL Server中删除但是已经有代码脚本使用它来演示新的SQL Server 11（Denali）的新功能：



• 使用OFFSET N ROWS FETCH NEXT N ROWS仅在SQL Server Denali中简单的页面

解决方案

spt_values 表在SQL Server文档中没有提及，但它可以追溯到Sybase日，而有一些极少的文档， p>

要查看如何使用它，请执行sp_helptext，并查看引用它的系统过程中
的文本。

换句话说，阅读代码并自己编写。

如果您扼制系统存储过程并检查表数据本身，则很清楚该表用于将代码转换为可读字符串（除其他外）。或者正如上面链接的Sybase文档所说，将内部系统值[...]转换成人类可读格式

表也是有时在MSDN博客的代码片段中使用，但从未在正式文档中 - 通常作为数字列表的方便来源。但是如其他地方所述，创建自己的数字来源更安全，更多可靠的解决方案比使用未记录的系统表。甚至一个Connect请求提供一个正确的，记录的号码表在SQL Server本身中。

无论如何，表完全没有文档，所以在了解任何内容方面没有什么重要价值，至少从实际的角度来看：下一个servicepack或升级可能会完全改变。知识的好奇心当然是： - ）

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

解决方案

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 :-)

这篇关于系统表master..spt_values的目的是什么，它的值的含义是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！