Conditions for memory optimized tables stored in off-row data table

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 decim

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)

StackExchange Database Administrators Q#171842, answer score: 5