为什么（以及如何）使用master..spt_values分割列？ [英] Why (and how) to split column using master..spt_values?

问题描述

将质疑的答案提请将一列拆分成多行我在这里重新写了这个[1]。

Type ='P'的含义是什么，为什么要使用未记录的master..spt_values拆分列？它的好处是什么？

---

[1]

  CREATE TABLE dbo.Table1 
（
 Col1 CHAR（1），
 Col2 CHAR（1），
 Col3 CHAR（1），
 Col4 VARCHAR 50）
）
 GO 
 
 INSERT INTO dbo.Table1 VALUES（'A'，'B'，'C'，'1,2,3'）
 GO 
 INSERT INTO dbo.Table1 VALUES（'D'，'E'，'F'，'6,7,8,9'）
 GO 
 
 
 SELECT 
 T.col1，RIGHT（LEFT（T.col4，Number-1），
 CHARINDEX（'，'，REVERSE（LEFT（'，'+ T.col4，Number-1） ））
 FROM 
 master..spt_values，
 table1 T 
 WHERE 
 Type ='P'AND Number BETWEEN 1 AND LEN（T.col4）+1 AND 
（SUBSTRING（T.col4，Number，1）='，'
  -  OR SUBSTRING（T.col4，Number，1）=''） - 无论如何， b $ b  

相关问题：

• 系统表表master..spt_values的用途和值的含义是什么？

解决方案

目的

为什么使用无记录的 master..spt-values

Sybase，因此它的混蛋儿MS SQL提供了各种功能和功能对于产品，这是在系统过程中实现的（而不是像sqlserver这样的二进制文件，作为服务启动）。这些系统过程过程用SQL代码编写，并命名为 sp _％。除了一些秘密内部构件，它们与其他任何SQL代码具有相同的限制和需求。它们是Sybase ASE或SQL Server产品的一部分。因此，它们不需要来记录它;并且内部位不能被合理地标记为未记录。

master..spt_values 包含所有各种位以及所述系统过程在SQL表中需要生成各种报告的部分。 sp 表示系统过程; spt 表示系统过程的表;当然的价值是内容。

查找表

什么是（含义）Type ='P'

人们经常描述 spt_values 为正规化，但这是不正确的术语。正确的术语是折叠或打包。它是26个逻辑的查找表，每个都精美地归一化，折叠成一个物理表，一个类型列来区分逻辑表。

现在在正常的数据库中，这将是一个严重的错误（只是查看一个查找表或许多的答案）。但是在服务器目录中，最好取代26个物理表。

• L代表LockType Lookup; V表示DeviceType Lookup（V为整个服务器中的Device为简称）;类型P2包含按位序列，用于扩展打包到INT中的位。




• 已知范围内的一组连续数字，是以SQL表的形式提供的，是为了执行一个Projection，其中许多系统程序都要做。类型P是0到2047之间的连续数字的列表。




• 此处使用术语投影作为技术上精确的意思是自然的逻辑意义，而不是关系代数的含义，这是不自然的。

因此只有一个目的对于 spt_values，包含26个折叠，否则分开的参考表和一个Projection表。

扩展

通常使用 spt_values ，作为普通的查询或参考或 ENUM 表。首先，查找值：

  SELECT *  -  list Genders 
 FROM Gender 
  

它的使用方式与Person具有需要扩展的GenderCode相同（非常扩展，这些奇怪的日子）：

  SELECT P. *， - 列表Person 
 G.Name  - 将GenderCode扩展为名称
 FROM Person P 
 JOIN性别G 
 ON P.GenderCode = G.GenderCode 
  

sp_lock 生成活动锁的报告，显示锁类型为字符串名称。但是， master..syslocks 包含锁定类型为数字，它不包含那些名称;如果这样做，那将是一个严重的非正规化表！如果执行查询（Sybase ASE代码，则必须转换）：

  SELECT *  - 列表LockTypes 
 FROM master..spt_values 
 WHERE type =L
  

你会注意到66 LockType查找表中的数字和名称。这允许 sp_lock 执行像Person :: Gender之类的简单代码：

  SELECT spid， -  list Active Locks 
 DB_NAME（dbid），
 OBJECT_NAME（id，dbid），
 v.name， - 展开锁定名称
页面，
 row 
 FROM master..syslocks L，
 master..spt_values LT 
 WHERE L.type = LT.number  -  
 AND type =L -  LockType查找表
订单1，2，3，4，5，6  - 这样易于理解
  

投影

Type ='P'的含义是什么？

什么是投影，它是如何使用的？

例如说，而不是上面查询产生的活动锁，你想要一个列表的所有 66 LockTypes，显示活动锁（或Null）的数量。你不需要一个游标，或者一个 WHILE 循环。我们可以通过 LockType查找表 活动锁计数：

  SELECT LT.name， -  list LockTypes 
 [Count] =（ -  with count 
 SELECT COUNT（*）
 FROM master..syslocks 
 WHERE type = LT.number 
）
 FROM master..spt_values LT 
 WHERE type =L
  

有几种方法，只有一种。另一个方法是使用派生表而不是子查询。但是您仍然需要投影。

这通常是用于扩展或投影的 spt_values 。现在你知道它在那里，你也可以使用它。它是安全的（在 master 数据库中），并被几乎所有的系统过程所使用，这意味着系统过程无法运行。

分割列？

啊，你不明白将一个CSV列分割成多行代码。

• 暂时关注 spt_values ，并再次检查该代码。它只需要一个连续数字的列表，以便可以逐个逐字节地逐列CSV列中的值列表。只有每个字节是一个逗号或字符串结尾才能激活代码。




• 在哪里可以获得一组连续的数字一个SQL表，而不是从头开始创建一个并将其插入到其中？为什么， master..spt_values 当然。如果你知道它在那里。




• （您可以通过阅读系统存储过程的代码了解一下ASE或SQL Server的内部结构请注意，一列中的任何CSV字段都是总标准化错误，它打破2NF（包含重复值）和1NF（不是原子）。注意，这是不包装或折叠的，它是一个重复组，它是不规范化的。这种严重错误的许多负面后果之一是，不是使用简单的SQL将重复组导航为行，而是必须使用复杂代码来确定和提取未规范化的CSV字段的内容。这里 spt_values P 为该复杂代码提供了一个向量，使其更容易。

有什么好处？

我想我已经回答了。如果没有，则需要数字列表的每个系统过程都必须创建一个临时表;并将行插入其中;在运行代码之前。当然，没有必要执行这些步骤，使系统程序更快。

现在，当你需要执行一个投影，例如。日历日期，或任何您可以使用 spt_values ，而不必每次创建自己的临时表（或创建您自己的私人永久表并维护它） 。

Subquestioning the answer to question "Split one column into multiple rows" which I re-wrote here as [ 1 ].

What is the (meaning of) Type = 'P' and why to use undocumented master..spt_values for splitting a column? What is the benefit of it?

---

[ 1 ]

CREATE TABLE dbo.Table1 
(
    Col1        CHAR(1),
    Col2        CHAR(1),
    Col3        CHAR(1),
    Col4        VARCHAR(50)
)
GO

INSERT INTO dbo.Table1 VALUES ('A','B','C','1,2,3')
GO
INSERT INTO dbo.Table1 VALUES ('D','E','F','6,7,8,9')
GO


SELECT
    T.col1, RIGHT(LEFT(T.col4,Number-1),
    CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
FROM
    master..spt_values,
    table1 T
WHERE
    Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
    (SUBSTRING(T.col4,Number,1) = ','
    -- OR SUBSTRING(T.col4,Number,1)  = '') --this does not work correctly anyway

Related question:

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

解决方案

Purpose

Why use undocumented master..spt-values

Sybase, and therefore its bastard son MS SQL, provide various features and functions for the product, that is implemented in system procedures (as opposed to the binaries like sqlserver, which are started as a service). These system procedures procedures are written in SQL code and named sp_%. Except for some secret internals, they have the same limitations and needs as any other SQL code. They are part of the Sybase ASE or SQL Server product. As such, they are not required to document it; and the internal bits cannot be reasonably labelled as "undocumented".

master..spt_values contains all the various bits and pieces that the said system procedures need, in an SQL table, to produce the various reports. The sp means system procedure; spt means tables for system procedures; and of course values is the content.

Lookup Tables

What is the (meaning of) Type = 'P'

People often describe spt_values as "de-normalised", but that is the incorrect term. The correct term is folded, or packed. It is 26 or so logical Lookup tables, each beautifully Normalised, folded into one physical table, with a Type column to differentiate the logical tables.

Now in a normal database, that would be a gross error (just look at the answers for "one lookup table or many"). But in a server catalogue, it is desirable, it replaces 26 physical tables.

• "L" stands for for LockType Lookup; "V" stands for DeviceType Lookup (V is short for Device throughout the server); etc. Type "P2" contains bitwise ordinals, for the expansion of bits that are packed into an INT.

• A set of consecutive numbers within known bounds, that is available in the form of an SQL table is required, in order to perform a Projection, which many of the system procedures have to do. Type "P" is a list of consecutive numbers between 0 and 2047.

• The term Projection is used here as the technically precise meaning, the natural logical sense, not the relational algebra meaning, which is unnatural.

There is therefore just one purpose for spt_values, to contain 26 folded, otherwise separate, Reference tables, and one Projection table.

Expansion

The ordinary use of spt_values then, is as an ordinary Lookup or Reference or ENUM table. First, the Lookup values:

    SELECT *                    -- list Genders
        FROM Gender 

It is used in the same way that Person has a GenderCode that needs to be expanded (very expanded, these freaky days):

    SELECT  P.*,                -- list Person
            G.Name              -- expand GenderCode to Name
        FROM Person P
        JOIN Gender G
            ON P.GenderCode = G.GenderCode

Eg. sp_lock produces a report of active locks, displaying lock types as string names. But master..syslocks contains lock types as numbers, it does not contain those names; and if it did, it would be a badly denormalised table ! If you execute the query (Sybase ASE code, you will have to convert):

    SELECT *                    -- list LockTypes
        FROM master..spt_values 
        WHERE type = "L"

you will notice 66 LockType numbers and names in the Lookup table. That allows sp_lock to execute simple code like Person::Gender above:

    SELECT  spid,               -- list Active Locks
            DB_NAME(dbid),
            OBJECT_NAME(id, dbid),
            v.name,             -- expand lock name
            page,
            row
    FROM master..syslocks   L,
         master..spt_values LT
    WHERE L.type = LT.number    -- 
    AND   type = "L"            -- LockType Lookup table
    ORDER by 1, 2, 3, 4, 5, 6   -- such that perusal is easy

Projection

What is the (meaning of) Type = 'P' ?

What is Projection and how is it used ?

Say, for example, instead of the active locks produced by the query above, you wanted a list of all 66 LockTypes, showing the number of active locks (or Null). You don't need a cursor, or a WHILE loop. We could Project the LockType Lookup table, through the count of active locks:

    SELECT  LT.name,            -- list LockTypes
            [Count] = (         -- with count
        SELECT COUNT(*)
            FROM master..syslocks
            WHERE type = LT.number
                )
        FROM master..spt_values LT
        WHERE type = "L"

There are several methods, that is just one. Another method is to use a Derived Table instead of the Subquery. But you still need the Projection.

That is typically what spt_values is used for, either Expansion or Projection. Now that you know it is there, you can use it too. It is safe (in the master database) and used by virtually all the system procedures, which means the system procedures cannot run without it.

for splitting a column?

Ah, you do not understand the "Split one CSV column into multiple rows" code.

• Forget about spt_values for a moment, and examine that code again. It just needs a list of consecutive numbers, so that in can step through the list of values in the CSV column, byte by byte. The code is activated only for each byte that is a comma, or end-of-string.

• Where to get a set of consecutive numbers in the form of an SQL table, rather than CREATing one from scratch and INSERTing into it? Why, master..spt_values of course. If you know it is there.

• (You can learn a bit about the internals of ASE or SQL Server, just by reading the code of the system stored procedures.)

• Note that any CSV field in one column is a gross Normalisation error, it breaks 2NF (contains repeating values) and 1NF (not atomic). Note, that is not packed or folded, it is a repeating group, it is un-normalised. One of the many negative consequences of such a gross error is, instead of using simple SQL to navigate the repeating group as rows, one has to use complex code to determine and extract the content of the un-normalised CSV field. Here spt_values P provides a vector for that complex code, making it easier.

What is the benefit of it?

I think I have answered that. If you did not have it, every system procedure that requires a list of Numbers would have to CREATE a temp table; and INSERT the rows into it; before running its code. Of course, not having to perform those steps, makes the system procedures much faster.

Now, when you need to perform a Projection, eg. calendar dates in the future, or whatever, you can use spt_values, instead of having to create your own temp table each time (or create your own private permanent table and maintain it).

这篇关于为什么（以及如何）使用master..spt_values分割列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！