3

Memory optimized tables store LOB types in internal tables.

I have a table with no explicit LOB types but when I run a query against sys.memory_optimized_tables_internal_attributes, I see varchar(255) columns listed with a type_desc value of 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

Running the following query yields 10 columns (COL_58, COL_64, COL_65, COL_67, COL_70, COL_72, COL_73, COL_75, COL_76, COL_79) listed as off-row data table

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;

I assume this storage decision is based on this table being super wide (nearly 10,400 bytes wide), yes?

SQL Server 2016 SP1, CU2, aka 13.0.4422.0

0
5

Bill you are absolutely correct and this is a new feature added in SQL 2016.

Table and Row Size in Memory-Optimized Tables

A memory-optimized table consists of a collection of rows and indexes that contain pointers to rows. In a memory-optimized table, in-row data cannot be longer than 8,060 bytes. However, starting SQL Server 2016 it is possible to create a table with multiple large columns (e.g., multiple varbinary(8000) columns) and LOB columns (i.e., varbinary(max), varchar(max), and nvarchar(max)). Columns that exceed the max size for in-row data are placed off-row, in special internal tables.

To prove that I took out 10 of you varchar(255) data type columns and recreated the table. Now I get zero record for the 2nd query. Because you row maximum row length becomes 7824 MB.

Now if you add one more column with varchar(255) you will see a single entry for internal off-row data table because maximum row length is over 8060 MB (8079 MB)

You can use this query to check your maximum row length.

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.