sql - 为什么(以及如何)使用master..spt_values拆分列？

Question

对问题"Split one column into multiple rows"的答案提出质疑，我在这里将其重写为[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)  = '') --this does not work correctly anyway

相关问题：


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

Answer 1

目的

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

Sybase及其产品的杂种MS SQL为产品提供了各种功能，这些功能是在系统过程中实现的（与像sqlserver这样的二进制文件一样，它们是作为服务启动的）。这些系统过程过程用SQL代码编写，并命名为sp_%.除了一些秘密内部知识外，它们与任何其他SQL代码一样具有相同的限制和需求。它们是Sybase ASE或SQL Server产品的一部分。因此，他们不需要记录下来；并且内部位不能合理地标记为“未记录”。

master..spt_values在SQL表中包含所述系统过程所需的所有各种位和片段，以产生各种报告。 sp表示系统过程； spt表示系统过程表；当然values是内容。

查找表

Type ='P'的含义是什么

人们通常将spt_values描述为“去规范化”，但这是不正确的术语。正确的术语是折叠或包装。它是26个左右的逻辑查找表，每个逻辑查找表都经过漂亮的标准化，折叠成一个物理表，并带有Type列以区分逻辑表。

现在在普通数据库中，那将是一个严重错误（只需查看“一个或多个查询表”的答案）。但是在服务器目录中，最好替换26个物理表。


“ L”代表LockType查找。 “ V”代表DeviceType查找（V是整个服务器中Device的缩写）；类型“ P2”包含按位顺序，用于扩展打包到INT中的位。
为了执行投影，需要一组在已知范围内的连续数字（以SQL表的形式可用），许多系统过程必须这样做。类型“ P”是一个介于0到2047之间的连续数字的列表。
术语“投影”在这里用作技术上的精确含义，即自然的逻辑含义，而不是不自然的关系代数含义。


因此，spt_values,仅有一个用途，即包含26个折叠的（否则是分开的）参考表和一个Projection表。

扩张

这样，spt_values的普通用法就是作为普通的Lookup或Reference或ENUM表。首先，查找值：

    SELECT *                    -- list Genders
        FROM Gender 

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

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

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

    SELECT *                    -- list 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,             -- 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

投影

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

什么是投影，如何使用？

举例来说，您需要一个包含所有66个LockType的列表，而不是上面的查询生成的活动锁，以显示活动锁的数量（或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"

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

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

拆分列？

嗯，您不理解“将一个CSV列拆分为多行”代码。


暂时忘记 spt_values，然后再次检查该代码。它只需要一个连续数字列表，以便可以逐字节浏览CSV列中的值列表。仅对每个逗号或字符串结尾的字节激活该代码。
在哪里以SQL表的形式获取一组连续的数字，而不是从头开始创建一个并插入其中？为什么， master..spt_values当然。如果您知道它在那里。
（您可以通过阅读系统存储过程的代码来了解一些有关ASE或SQL Server的内部信息。）
请注意，一栏中的任何CSV字段都是严重的规范化错误，它会破坏2NF（包含重复值）和1NF（不是原子）。请注意，它不是包装或折叠的，它是一个重复的组，未标准化。这种严重错误的许多负面后果之一是，必须使用复杂的代码来确定和提取非标准化CSV字段的内容，而不是使用简单的SQL将重复的组导航为行。在这里， spt_values P为该复杂代码提供了一个向量，使其更容易。


它有什么好处？

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

现在，当您需要执行投影时，例如您可以使用 spt_values，而不是每次都创建自己的临时表（或创建自己的私有永久表并对其进行维护）。