DBCC Page rowsize

Question

I have a table with

CREATE TABLE dbo.NOT_UNIQUE ( ID INT NULL , COL1 INT NULL, COL2 CHAR(10) NULL );

But why the rowsize is 33?? byte?

int should be 4byte, CHAR(10) 10 bytes,,,, and including the uniqueidentifier hidden column 4byte.... 22 byte only.

Answer 1

OK, good. You now posted a different table from your first post, since I now see that your table has a clustered index.

Half of the rows has a length of 25. That is explained by my earlier answer. Each of them are the first numerical value for ID. Note that the uniquifier isn't populated for the "first row"; it is only populated when you have duplicates for the clustered index key.

Then you have a duplicate of ID. That second row has a length of 33. The uniquifier is 4 bytes. 25 + 4 = 29. 4 more bytes to explain.

A row where the uniquifier exists is handled like a row with at least one variable size column. That means that you have 2 bytes specifying the number of variable length column. And then also 2 bytes for each var size column, for the column offset . In your case you have 1 var size column. I.e., 2 + 2 = 4.

Mystery explained. See for instance this for more info: https://aboutsqlserver.com/2013/10/15/sql-server-storage-engine-data-pages-and-data-rows/

Answer 2

Please post a full repro. The output you post is not for the CREATE TABLE you posted. The CREATE TABLE you posted is a heap, and a heap doesn't have a uniquifier.

For the CREATE TABLE you posted, I get the length 25. The data is 4+4+10 = 18. So we have a row overhead of 7 bytes (25-18). The row overhead is:

2 bytes in beginning of row (tag A and tag B).
2 byte specifying length of the fix portion of the row.
2 bytes specifying number of columns.
1 byte being the NULL bitmap.

As you see, above lines up exactly, 4+4+10 + 2+2+2+1 = 25. Below is T-SQL code I used (in a database named TSQL). For an answer corresponding to your example, please post a repro, with the correct DDL and DML commands (as I do below):

CREATE TABLE dbo.NOT_UNIQUE ( ID INT NULL , COL1 INT NULL, COL2 CHAR(10) NULL );

insert into NOT_UNIQUE values(1, 1, '1')
insert into NOT_UNIQUE values(1, 1, '1')
insert into NOT_UNIQUE values(2, 2, '2')

--Get the page no
SELECT --*
 allocated_page_file_id
,allocated_page_page_id
,object_id
,partition_id
,allocation_unit_type_desc
,page_type
,page_type_desc
,index_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('NOT_UNIQUE'), null, null, 'DETAILED');

DBCC TRACEON(3604)
DBCC PAGE(TSQL, 1, 872, 1) WITH TABLERESULTS