by what size will nvarchar(max) column get stored in LOB_DATA allocation unit?

• Marek Grzymala

  SSCommitted

  Points: 1946

  More actions

  May 26, 2018 at 9:42 am

  #360077

  I have a table defined as follows:
  
CREATE TABLE [TestTable]
(
  Col1 INT IDENTITY PRIMARY KEY,
  Col2 NVARCHAR(MAX) NULL DEFAULT REPLICATE('A', 7000)
)
GO

  If I increase my  DEFAULT value for Col2 by one byte (to 4001) then my Col2 gets stored (after filling table with data) in the LOB_DATA allocation unit, rather than in IN_ROW_DATA:
  
  
INSERT dbo.[TestTable] DEFAULT VALUES;
GO 1000

  SELECT
    object_name(object_id) AS name
    , partition_id
    , partition_number AS pnum
    , rows
    , allocation_unit_id AS au_id
    , type_desc as page_type_desc
    , total_pages AS pages
FROM
    sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    object_id=object_id('dbo.TestTable');
GO

  Something must be wrong with my math below because I am getting only 8030 bytes per record so why exactly 4001 triggers the 8,060 byte-per-page-limit as explained by Kalen Delaney here. ?
  Can anyone help?

  Column		Size in bytes per column:
  Col1 INT size:		4
  Col2 NVARCHAR(MAX) size:	(4000 * 2) +	8000
  	2 additional bytes per max column:	2
  		24
  Total:		8030

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  May 27, 2018 at 10:09 am

  #1992074

  Marek Grzymala - Saturday, May 26, 2018 9:42 AM

  I have a table defined as follows:
  CREATE TABLETABLE [TestTable] [TestTable]
((
Col1        Col1    INTINT IDENTITYIDENTITY PRIMARYPRIMARY KEYKEY,,
Col2        Col2    NVARCHARNVARCHAR((MAXMAX)) NULLNULL DEFAULTDEFAULT REPLICATEREPLICATE(('A''A',, 4000 4000))
))
GOGO
  If I increase my  DEFAULT value for Col2 by one byte (to 4001) then my Col2 gets stored (after filling table with data) in the LOB_DATA allocation unit, rather than in IN_ROW_DATA:
  
  
  INSERT dbo.[TestTable] DEFAULT VALUES;
  GO 1000

  SELECT
    object_name(object_id) AS name
    , partition_id
    , partition_number AS pnum
    , rows
    , allocation_unit_id AS au_id
    , type_desc as page_type_desc
    , total_pages AS pages
FROM
    sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    object_id=object_id('dbo.TestTable');
GO

  Something must be wrong with my math below because I am getting only 8030 bytes per record so why exactly 4001 triggers the 8,060 byte-per-page-limit as explained by Kalen Delaney here. ?
  Can anyone help?

  Column		Size in bytes per column:
  Col1 INT size:		4
  Col2 NVARCHAR(MAX) size:	(4000 * 2) +	8000
  	2 additional bytes per max column:	2
  		24
  Total:		8030

  What a lot of people don't understand is that there's a header on each row to identify the content on each row.  Yes, it contains the extra 2 bytes for each variable length datatype in the row but it contains more than that.

  For example, I recently did some experiments with a table that contains only a GUID column (fixed width at 16 bytes) and a CHAR(100) column (fixed width at 100 bytes) for a total of 116 bytes and yet sys.dm_db_index_physical_stats lists the row size as 123 bytes (7 bytes longer than you might otherwise expect).  There's extra overhead in the header bytes of each row.

  A pretty good article on the subject of the row-header can be found at the following URL.
  https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-storage-internals-101/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  May 29, 2018 at 11:43 am

  #1992203

  Marek Grzymala - Saturday, May 26, 2018 9:42 AM

  I have a table defined as follows:
  
CREATE TABLE [TestTable]
(
  Col1 INT IDENTITY PRIMARY KEY,
  Col2 NVARCHAR(MAX) NULL DEFAULT REPLICATE('A', 7000)
)
GO

  If I increase my  DEFAULT value for Col2 by one byte (to 4001) then my Col2 gets stored (after filling table with data) in the LOB_DATA allocation unit, rather than in IN_ROW_DATA:
  
  
  INSERT dbo.[TestTable] DEFAULT VALUES;
  GO 1000

  SELECT
    object_name(object_id) AS name
    , partition_id
    , partition_number AS pnum
    , rows
    , allocation_unit_id AS au_id
    , type_desc as page_type_desc
    , total_pages AS pages
FROM
    sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    object_id=object_id('dbo.TestTable');
GO

  Something must be wrong with my math below because I am getting only 8030 bytes per record so why exactly 4001 triggers the 8,060 byte-per-page-limit as explained by Kalen Delaney here. ?
  Can anyone help?

  Column		Size in bytes per column:
  Col1 INT size:		4
  Col2 NVARCHAR(MAX) size:	(4000 * 2) +	8000
  	2 additional bytes per max column:	2
  		24
  Total:		8030

  Not sure that it would matter what else was in your table, as nvarchar(4000) is the largest possible width for an nvarchar column before you have to use nvarchar(max).   I just tried the following and it gets a syntax error, so I'm pretty sure you can't have an nvarchar(4001) column to begin with.
  CREATE TABLE #TEST
    ID int,
    TEST_COL nvarchar(4001)
);

  Also, the following get's an error as well:
  DECLARE @TestVar AS nvarchar(4001);
  The error is:
  Msg 2717, Level 16, State 2, Line 1
The size (4001) given to the parameter '@TestVar' exceeds the maximum allowed (4000).

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeffrey Williams

  SSC Guru

  Points: 90244

  More actions

  May 29, 2018 at 1:49 pm

  #1992213

  Jeff Moden - Sunday, May 27, 2018 10:09 AM

  What a lot of people don't understand is that there's a header on each row to identify the content on each row.  Yes, it contains the extra 2 bytes for each variable length datatype in the row but it contains more than that.

  For example, I recently did some experiments with a table that contains only a GUID column (fixed width at 16 bytes) and a CHAR(100) column (fixed width at 100 bytes) for a total of 116 bytes and yet sys.dm_db_index_physical_stats lists the row size as 123 bytes (7 bytes longer than you might otherwise expect).  There's extra overhead in the header bytes of each row.

  A pretty good article on the subject of the row-header can be found at the following URL.
  https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-storage-internals-101/

  There are also other factors - there will be a 14-byte overhead per row if you have enabled RCSI, snapshot isolation or if the database is part of an AOAG with a readable secondary.  This 14-byte overhead is for versioning

  Here is a decent outline: http://aboutsqlserver.com/2013/10/15/sql-server-storage-engine-data-pages-and-data-rows/

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  May 29, 2018 at 4:08 pm

  #1992244

  by what size will nvarchar(max) column get stored in LOB_DATA allocation unit?

  When SQL decides it's necessary to fit the row into the page.  There's no way to be sure ahead of time which rows SQL will force out and which not.  Unless, of course, you could force all max values to be stored out-of-row, but that's obviously not what you want here.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.