SQL Server – Identical Tables with Different Record Sizes

database-internalssql serversql-server-2012

Identical tables, identical columns, identical records, but why different record sizes? 

USE [test]
GO

CREATE TABLE [dbo].[mybit](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
    [col9] [bit] NULL
) 
GO

INSERT INTO [dbo].[mybit]
VALUES (1,1,1,1,1,1,1,1,NULL)
GO 2

DBCC IND(test, mybit, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

Both records are 10 bytes in size.

enter image description here

CREATE TABLE [dbo].[mybit2](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
) 
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1)
GO

ALTER TABLE [dbo].[mybit2] ADD [col9] [bit] NULL
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1, NULL)
GO

DBCC IND(test, mybit2, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

One record is 9 bytes in size and another is 10.

enter image description here

Best Answer

Thomas Cleberg is correct.

The first row you insert into bit2 shows up in DBCC page as

10000500 ff080000 63

This breaks down as follows

+-------+-----------------+--------+-------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                Comments               |
+-------+-----------------+--------+-------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                  |
| 1     | 00              | 0x00   | TagB                                                  |
| 2-3   | 0500            | 0x0005 | Column Count offset 0x0005 = 5                        |
| 4     | ff              | 0xff   | The bit values. All are 1. 11111111 in binary = 0xff  |
| 5-6   | 0800            | 0x0008 | The column count. 0x0008 = 8. You have 8 columns.     |
| 7     | 00              | 0x00   | The Null bitmap. None are NULL.                       |    
| 8     | 63              | 0x63   | Spare                                                 |
+-------+-----------------+--------+-------------------------------------------------------+

(If you wonder why there is a "spare" byte. This is explained here. It is to allow the row to be replaced by a forwarding pointer should the need arise. The value of it is arbitrary. For me it was 0x63. For you it looks like it was 0x00)

After you alter the table and add a new column with a NULL value (or a runtime constant default value in SQL Server 2012+ enterprise edition) then this is a metadata only change. SQL Server doesn't update existing rows. It can deduce from the column count stored in the row that the value for col9 should be NULL for that row. The change will be written out next time the row is updated.

Newly inserted rows will get written out in full though. Two extra bytes are needed because the single byte in the fixed data section is full up with values for bit columns 1-8 so an additional byte is needed for bit columns 9 to 16. Additionally the Null bitmap grows by a byte too. However there is no need to retain that "spare" byte to keep the rowsize at a minimum level so the net effect is an increase in one byte.

+-------+-----------------+--------+------------------------------------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                      Comments                                      |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                                               |
| 1     | 00              | 0x00   | TagB                                                                               |
| 2-3   | 0600            | 0x0006 | Column Count offset 0x0006 =6                                                      |
| 4-5   | ff00            | 0xff00 | The bit values. An extra byte has now been added to accomodate the 9th bit column. |
| 6-7   | 0900            | 0x0009 | The column count. You now have 9 columns.                                          |
| 8-9   | 0001            | 0x0100 | The Null bitmap. The 9th column is NULL. 100000000 in binary is 0x0100             |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
Related Question