May 26, 2018 at 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 |
May 27, 2018 at 10:09 am
Marek Grzymala - Saturday, May 26, 2018 9:42 AMI 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 1000SELECT
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
Change is inevitable... Change for the better is not.
May 29, 2018 at 11:43 am
Marek Grzymala - Saturday, May 26, 2018 9:42 AMI 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 1000SELECT
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)
May 29, 2018 at 1:49 pm
Jeff Moden - Sunday, May 27, 2018 10:09 AMWhat 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
May 29, 2018 at 4:08 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply