使用专用表类型

  • 12 分钟

SQL Server 支持专用表类型，这些类型专为特定方案和工作负荷设计，超出标准基于磁盘的表。 这些表类型（包括 内存中、 临时、 外部、 LEDGER 和 GRAPH）解决了标准表无法有效解决的特定性能、符合性或体系结构难题。

了解何时以及如何使用这些专用表类型对于设计满足应用程序要求的有效数据库解决方案至关重要。

使用优化的内存表

传统的基于磁盘的表会导致磁盘 I/O 延迟，即使缓存也是如此。 对于需要高速的方案（例如每秒数千个具有毫秒响应时间的事务），磁盘延迟成为瓶颈。 内存中表通过将数据完全保存在 RAM 中，并采用无锁乐观并发控制，消除了这一问题。

了解何时使用内存中表

内存中优化表 为特定工作负荷提供显著的性能优势：

  • 会话状态存储 - 具有数百万个并发会话的 Web 应用程序
  • 实时分析 - 需要微秒延迟的金融交易系统
  • 高频率 OLTP - 订单处理系统处理 10,000 多个事务/秒
  • 缓存层 - 经常访问的引用数据（产品目录、配置）
  • 暂存表 - 具有大量插入/更新作 的 ETL 进程

例如，一个电子商务网站使用内存表存储购物车信息，可以处理5万个并发购物车，其响应时间为亚毫秒级，从而将结帐延迟减少了80%。

考虑权衡

内存中表将实际表数据存储在 RAM 中，以便更快地访问，而传统表将数据存储在磁盘上。 但是，数据大小受可用 RAM 的限制，这些表不支持大型对象类型，例如 VARCHAR(MAX)， NVARCHAR(MAX)或 VARBINARY(MAX)。

即使表数据位于内存中，SQL Server 仍会将事务日志写入磁盘，以确保持久性。 这意味着，如果服务器重启，则不会丢失提交的事务， 数据将从事务日志恢复回内存。

可以通过使用 MEMORY_OPTIMIZED = ON 选项创建在内存中优化的表。 下面是一个示例：

-- Create in-memory optimized table
CREATE TABLE dbo.OrderCache (
    OrderID INT PRIMARY KEY NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME2,
    Amount DECIMAL(10,2),
    INDEX IX_CustomerID NONCLUSTERED (CustomerID)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

使用时态表

临时表 会自动跟踪数据更改的完整历史记录。 更新或删除行时，SQL Server 会自动将以前的版本存储在链接历史记录表中，其中包含显示该版本有效时间的时间戳。 以透明的方式，你可以使用普通的INSERT、UPDATE语句和DELETE语句来修改数据，数据库引擎负责处理版本控制。

主要优势在于可以查询数据在任意时间点的状态。 可以询问“2025 年 1 月 1 日员工工资是什么？”或“显示上季度库存的所有产品”，而无需维护复杂的审核表或编写自定义版本控制逻辑。

临时表满足合规性、故障排除和分析需求：

  • 合规性和审核 - 财务记录需要完整的更改历史记录
  • 故障排除 - 调查发生争议交易时的帐户余额
  • 趋势分析 - 分析产品价格在季度的变化情况
  • 数据恢复 - 在不还原备份的情况下还原意外更新
  • 缓慢变化维度 - 自动实现数据仓库 2 型维度

常见业务方案包括跟踪工资变化和促销的应用程序、库存管理分析库存趋势、医疗保健维护患者记录历史记录以符合性，以及保险跟踪保险覆盖范围变更，以便解决纠纷。

考虑时态表的优势

临时表需要零应用程序代码更改并提供透明历史记录跟踪。 时间点查询使用简单语法，自动清理可管理旧历史数据。 但是，临时表大约是存储要求的两倍。

临时表 会自动维护数据更改的完整历史记录，以便进行审核和时间点分析。

可以使用该选项创建临时表 SYSTEM_VERSIONING = ON 。 临时表需要两个额外的 DATETIME2 列来跟踪每个行版本的有效期，以及一个 PERIOD FOR SYSTEM_TIME 子句来定义哪些列跟踪这些时间戳。 下面是一个示例：

-- Create temporal table with automatic history tracking
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Department NVARCHAR(50),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2026-01-01' 
WHERE EmployeeID = 1;

创建临时表时，SQL Server 会自动创建历史记录表来存储以前的行版本，并透明地管理这两个表。

使用外部表

现代数据体系结构通常将数据分散在数据湖、Blob 存储和多个系统中。 传统上，必须先将所有数据提取、转换、加载到数据库中，然后再对其进行查询。 外部表使 数据虚拟化 能够查询数据，而无需移动数据，从而节省存储成本和 ETL 复杂性。

了解何时使用外部表

外部表擅长跨分布式存储系统查询数据：

  • Data Lake 集成 - 在不导入的情况下查询 Azure Data Lake Storage 中的 Parquet/CSV 文件
  • 数据浏览 - 在决定导入内容之前分析原始数据
  • 成本优化 - 避免复制已存储在其他位置的数据
  • 联合查询 - 将数据库表与外部系统中的文件联接
  • 存档存储 - 访问存储在更便宜的 Blob 存储中的历史数据

常见方案包括查询数据湖中的日志文件数年以及事务数据、将实时数据库记录与存档的 Blob 存储数据相结合、在不完全迁移的情况下访问旧数据，以及查询数百万个 IoT 传感器 JSON 文件，而无需导入。

考虑性能约束

外部表提供跨源的统一查询，但存在限制：

  • 无数据移动或存储重复
  • 由于网络延迟和文件解析，通常比本机表慢
  • 只读（在大多数情况下无法更新/删除）
  • 有限的索引和优化

可以使用具有数据源和文件格式的 CREATE EXTERNAL TABLE 语句创建外部表。 下面是一个示例：

-- Create external table pointing to data lake
CREATE EXTERNAL TABLE dbo.ExternalSalesData (
    OrderID INT,
    CustomerID INT,
    OrderAmount DECIMAL(10,2),
    OrderDate DATE
) WITH (
    LOCATION = '/raw/sales/',
    DATA_SOURCE = DataLakeSource,
    FILE_FORMAT = ParquetFormat
);

使用账本表

在受管制行业，证明数据未被篡改非常重要。 传统数据库可以让管理员修改数据、进行回溯更改或删除审核日志。 账本表使用受区块链技术启发的 加密验证 来创建可独立验证的防篡改记录，从而提供数据完整性的加密证明。

了解何时使用账本表

账本表满足法规合规性和取证审核需求：

  • 金融交易 - 银行、付款处理、加密货币交易所
  • 供应链 - 跟踪产品来源、保管和真实性
  • 法律记录 - 合同、协议、需要不可变的法律文件
  • 医疗保健 - 处方记录、患者同意表单
  • 政府 - 投票记录、土地登记处、许可证发放

例如，银行可以使用账本表来存储交易记录，允许审核员验证发布后没有更改任何交易。 供应链公司可以使用账本表跟踪产品来源，为客户提供真实性证明。

在可更新账本和仅追加账本之间进行选择

账本表分为两种类型。 可更新账本表允许INSERT、UPDATE和DELETE操作，同时以加密方式跟踪所有更改。 系统会自动将以前的版本存储在历史记录表中，类似于临时表，但具有防篡改验证的附加优势。 仅追加分类账表仅允许 INSERT 操作，为需要绝对数据完整性的场景创建真正不可变的记录。

可以通过创建既可更新账本表又是临时表的表来组合这两种技术，同时获得加密验证以及时间点查询功能。

例如，制药公司对临床试验数据使用仅追加的账本表，并为独立审核员提供加密证明，以保证测试结果在提交后未被更改。

可以使用此选项创建账本表 LEDGER = ON 。 下面是一个示例：

-- Create ledger table
CREATE TABLE dbo.FinancialTransaction (
    TransactionID INT PRIMARY KEY IDENTITY,
    AccountNumber NVARCHAR(20),
    Amount DECIMAL(15,2),
    TransactionType NVARCHAR(20)
) WITH (LEDGER = ON);

-- Append-only ledger provides immutability
CREATE TABLE dbo.AuditLog (
    LogID INT PRIMARY KEY IDENTITY,
    EventDescription NVARCHAR(500),
    EventTimestamp DATETIME2
) WITH (LEDGER = ON, APPEND_ONLY = ON);

创建账本表时，SQL Server 会自动添加隐藏列，并创建支持的数据库对象来跟踪加密链。 每次行修改都会生成一个加密哈希，该哈希链接到以前的操作，从而创建一个防篡改的审计日志。 可以使用内置系统视图（如 sys.database_ledger_transactions ）和 sp_verify_database_ledger 等过程来验证数据完整性，以验证加密链是否保持未中断。

使用图形表

关系数据库擅长结构化数据，但很难与需要许多联接的高连接数据作斗争。 使用传统表查找“朋友的朋友”或“通过 3 级类别关联的产品”会变得复杂。 SQL Graph 功能以本机方式对 节点 （实体）和 边缘 （关系）建模，使复杂的关系查询变得简单且高性能。

图形表简化了关系建模，但需要学习新的语法。 它们提供连接的数据的直观建模、用于关系遍历的更简单查询，以及多跃点查询的更好性能。 灵活的架构可适应不断发展的关系。 然而，图形表的 MATCH 语法存在学习曲线，且最适合读取密集型关系查询。

数据库可以包含多个节点和边缘表，这些表协同工作来对图形数据进行建模。 定义哪些表表示节点，以及哪些表基于数据关系表示边缘。

注释

图表并非在每种情况下都是最佳选择。 如果外键可正常工作的简单父子关系、主要是无复杂关系的事务数据，或高度结构化的稳定架构，则避免使用图形表。

了解图形表结构

SQL Graph 使用两种类型的表对关系进行建模。 节点表 存储实体，并自动包含唯一标识每个节点的隐藏 $node_id 列。 边缘表 存储节点之间的关系，并包括隐藏列 $edge_id， $from_id以及 $to_id 维护连接。 这些特殊列使 MATCH 语法能够有效地遍历关系。

可以通过使用AS NODE和AS EDGE语法创建图形表。 下面是一个示例：

-- Create graph tables
CREATE TABLE Person AS NODE;
CREATE TABLE Manages AS EDGE;
CREATE TABLE Knows AS EDGE;

-- Insert nodes
INSERT INTO Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

-- Insert edges (relationships)
INSERT INTO Manages VALUES (1, 2), (2, 3);

-- Query relationships
SELECT Person1.name, Person2.name 
FROM Person AS Person1, Manages, Person AS Person2
WHERE MATCH (Person1-(Manages)->Person2)
AND Person1.id = 1;

创建节点表和边缘表时，SQL Server 会自动管理启用高效图形遍历查询的隐藏系统列。

每个专用表类型都有权衡：内存中表需要 RAM、临时表双重存储、外部表添加网络延迟、账本表防止删除，而图形表需要新的语法。 建议在设计过程中选择正确的表类型，因为这些决策在部署后很难更改。