SQL Server - 使用聚集索引时如何存储数据页

Question

billinkc

Asked: 2017-04-25 07:19:19 +0800 CST2017-04-25 07:19:19 +0800 CST 2017-04-25 07:19:19 +0800 CST

行外数据表存储内存优化表的条件

772

内存优化表将 LOB 类型存储在内部表中。

我有一个没有显式 LOB 类型的表，但是当我对sys.memory_optimized_tables_internal_attributes运行查询时，我看到列出的 varchar(255) 列的 type_desc 值为INTERNAL OFF-ROW DATA TABLE。

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

SET ANSI_PADDING ON;
GO

CREATE TABLE dbo.DERP
(
    RECORD_KEY numeric(30, 0) NOT NULL
,   COL_1 bigint NOT NULL
,   COL_2 datetime2(0) NOT NULL
,   COL_3 datetime2(0) NOT NULL
,   COL_4 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_5 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_6 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_7 date NULL
,   COL_8 bigint NULL
,   COL_9 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_10 int NULL
,   COL_11 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_12 int NULL
,   COL_13 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_14 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_15 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_16 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_17 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_18 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_19 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_20 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_21 bigint NULL
,   COL_22 bigint NULL
,   COL_23 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_24 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_25 int NULL
,   COL_26 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_27 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_28 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_29 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_30 decimal(7, 4) NULL
,   COL_31 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_32 decimal(8, 4) NULL
,   COL_33 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_34 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_35 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_36 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_37 int NULL
,   COL_38 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_39 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_40 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_41 int NULL
,   COL_42 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_43 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_44 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_45 int NULL
,   COL_46 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_47 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_48 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_49 int NULL
,   COL_50 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_51 int NULL
,   COL_52 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_53 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_54 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_55 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_56 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_57 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_58 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_59 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_60 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_61 date NULL
,   COL_62 char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_63 int NULL
,   COL_64 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_65 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_66 int NULL
,   COL_67 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_68 int NULL
,   COL_69 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_70 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_71 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_72 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_73 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_74 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_75 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_76 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_77 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_78 char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_79 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_80 bigint NULL
,   CONSTRAINT dbo_DERP
        PRIMARY KEY NONCLUSTERED
        (
            RECORD_KEY ASC
        )
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

SET ANSI_PADDING OFF;
GO

运行以下查询会产生 10 列（COL_58、COL_64、COL_65、COL_67、COL_70、COL_72、COL_73、COL_75、COL_76、COL_79）列为行外数据表

SELECT
    QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(moa.object_id)) AS 'table'
,   c.name AS 'column'
,   c.max_length
,   moa.type_desc
FROM
    sys.memory_optimized_tables_internal_attributes moa
    INNER JOIN
        sys.columns c
        ON moa.object_id = c.object_id
           AND moa.minor_id = c.column_id
    INNER JOIN
        sys.objects o
        ON moa.object_id = o.object_id
WHERE
    o.name = 'DERP'
ORDER BY
    1;

我假设这个存储决定是基于这个超宽的表（将近 10,400 字节宽），是吗？

SQL Server 2016 SP1，CU2，又名 13.0.4422.0

1 个回答

Voted

SqlWorldWide · Answer 1 · 2017-04-25T09:22:56+08:00

Bill，你是绝对正确的，这是 SQL 2016 中添加的新功能。

内存优化表中的表和行大小

内存优化表由一组行和包含行指针的索引组成。在内存优化表中，行内数据不能超过 8,060 字节。但是，从 SQL Server 2016 开始，可以创建包含多个大列（例如，多个 varbinary(8000) 列）和 LOB 列（即，varbinary(max)、varchar(max) 和 nvarchar(max)）的表。超过行内数据最大大小的列被放置在行外，在特殊的内部表中。

为了证明我取出了你们中的 10 个 varchar(255) 数据类型列并重新创建了表。现在我得到第二个查询的零记录。因为您的行最大行长度变为 7824 MB。

现在，如果您使用 varchar(255) 再添加一列，您将看到内部行外数据表的单个条目，因为最大行长度超过 8060 MB（8079 MB）

您可以使用此查询来检查最大行长度。

SELECT OBJECT_NAME (id) tablename
     , COUNT (1)        nr_columns
     , SUM (length)     maxrowlength
FROM   syscolumns
WHERE ID =OBJECT_ID('[dbo].[DERP]')
GROUP BY OBJECT_NAME (id)
ORDER BY OBJECT_NAME (id)

行外数据表存储内存优化表的条件

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

行外数据表存储内存优化表的条件

1 个回答

相关问题