第三章 数据类型 本章内容包括： 数据类型的重要性 使用错误标准数据类型的后果 使用高级数据类型的原因 处理 XML 和 JSON 数据的好处 错误6# 始终将整数存储为 INT 想象一下，我们有一个大型数据仓库。一个事实表有10亿行，并且与五个维度表关联，这些维度表各有30,000行。由于缓冲区缓存中的数据量大，性能很差且内存总是满的。当查询运行时，大量数据被写入TempDB。我们已经优化了查询，也已经审查了索引策略，并确保索引和统计信息都得到了良好的维护。看起来唯一能做的事情就是增加更多的硬件，但根据过去两年的趋势，我们怀疑如果增加更多的内存，只会将问题推到下一阶段。我们应该怎么做？一个起点是考虑审查我们的数值数据类型，特别是那些在主键/外键关系中使用的类型。 INT 是 SQL Server 中使用最广泛但也最容易被误用的数据类型。实际上，我们有四种专门用于存储整数的数据类型。这些数据类型在表中有详细说明。 整数类型 Data type Range Size (Bytes) TINYINT 0 to 255 1 SMALLINT –32,768 to 32,767 2 INT –2,147,483,648 to 2,147,483,647 4 BIGINT –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8 SELECT DATALENGTH(CAST(1 AS TINYINT)) AS TinyIntSize , DATALENGTH(CAST(1 AS SMALLINT)) AS SmallIntSize , DATALENGTH(CAST(1 AS INT)) AS IntSize , DATALENGTH(CAST(1 AS BIGINT)) AS BigIntSize ; TinyIntSize SmallIntSize IntSize BigIntSize 1 2 4 8 假设我们的五个维度表在其主键列中使用 INT 类型。每个维度表有 30,000 行，而 SMALLINT 数据类型可以表示的正值超过 32,000。这意味着如果我们预期维度不会大幅增长，那么我们可以在每行中节省 2 个字节。 此时，你可能会想，“我们为什么要在意节省2字节呢？”这个问题的答案需要一些简单的数学。每个五个维度表中都有30,000行。通过改用 SMALLINT，每个表我们只会节省 58 KB。但我们的事实表有 10 亿行。这意味着每个键可以节省 1.86 GB。将其乘以五个维度表，这意味着对于每个查询涉及事实表的所有行和所有五个键，我们将节省 9.3 GB。再将其扩展到数据仓库中的八个事实表。我们还应考虑索引的大小，这些索引是建立在这些外键列上的。现在考虑运行不同查询的并行会话。突然间，我们的数据类型选择对内存消耗产生了直接且显著的影响。 错误7# 始终使用可变长度字符串 想象一下，我们有一个存储美国地址的表。我们正努力确保数据尽可能节省空间。因此，我们对所有列使用可变长度字符串，包括地址的每一行以及邮政编码。我们知道缅因州的 Mooselookmeguntic 市和宾夕法尼亚州的 Kleinfeltersville 市拥有美国最长的城市名称，每个都有 17 个字符，所以我们将 CityName 列设置为 VARCHAR(17)。我们知道美国最长的州名是 Rhode Island and Providence Plantations，所以我们将 state 列设置为 VARCHAR(48)。我们知道邮政编码正好是 10 个字符，但因为我们知道每次长度都相同，我们应该使用 VARCHAR(10) 还是 CHAR(10)？这真的重要吗？无论哪种方式，数据长度都是 10 个字符，所以应该占用 10 个字节的空间，对吗？如果这是正确的，那么我们为什么还需要固定长度的字符串呢？事实是，这个假设并不正确。要理解原因，我们需要了解一些 SQL Server 是如何存储数据的。 SQL Server 将数据存储在一系列 8 KB 的页中，每八页组成一个 64 KB 的区块，这通常是读取的最小数据量。每个数据页都有一个 96 字节的页头，用于存储适用于整个页的信息，例如其唯一 ID 以及所属表（或索引）的对象 ID。 形成行的数据随后存储在页面上的槽中。然而，这些槽不仅存储数据。它们还必须存储少量元数据，以使数据有用。这些元数据包括关于存储在该槽中的记录类型的信息。例如，它是数据记录还是索引记录？它是否包含幽灵数据（已被逻辑删除但尚未实际移除的数据）？ 其他元数据包括固定长度数据的长度（这不仅包括固定长度的字符数据，还包括整数等数据）、用于跟踪可变长度列是否包含 NULL 值的 NULL 值位图，以及版本标签，该标签用于诸如在线索引重建或具有乐观事务隔离级别的事务等操作。我们将在第 10 章讨论隔离级别。 然而，我们在这里真正关心的元数据部分是列偏移数组。它用于跟踪每个可变长度列在行中的起始位置。由于可变长度数据的长度可以是任意的，因此这个偏移表是 SQL Server 区分一段数据结束与下一段数据开始的唯一方式。 每个可变长度列在此表中都需要一个 2 字节的偏移量，这意味着每个可变长度列比固定长度列多使用 2 字节的空间。即使该列存储的是 NULL 值，这条规则仍然适用。因此，如果我们对邮政编码使用 CHAR(10)，它将占用 10 字节的空间，但如果使用 VARCHAR(10)，则会占用 12 字节的空间，尽管实际数据长度为 10 字节。 错误8# 编写你自己的层级代码 如果我们查看员工表，可能已经意识到 EmployeeID 和 ManagerID 列是用来表示员工层级的。因此，请参考图 3.2 中的组织架构图，它展示了 MagicChoc 高级管理团队的组织结构。 […]
