2

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

4

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             |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
  • Great answer Martin! I always thought that NULL bitmap is minimum 2 bytes long though. Could you please clarify on how you got it 1 bytes long in your tables before I accept it as an asnwer? – Pavel Nefyodov Dec 15 '14 at 12:37
  • @PavelNefyodov - The easiest way to see that would be to create a table with less than 8 columns but for which the row size would be above the 9 byte minimum. Then you no longer have that unused byte confusing things. Or see Paul Randal's example here where it happens to use a single byte. (Byte 10 in his case) – Martin Smith Dec 15 '14 at 12:49
  • I have no problem with an extra "spare" byte. I think your explanation on that is brilliant. I am just a bit confused with bytes 5-6 and 7 in your first table. My understanding was that [NULL bitmap is minimum 2 bytes long] [1]. [1]: technet.microsoft.com/en-us/library/ms178085(v=sql.110).aspx – Pavel Nefyodov Dec 15 '14 at 12:56
  • @PavelNefyodov - Probably they are including the column count offset AKA NULL bitmap offset (depends where you are counting from) in that Null_Bitmap as otherwise the fixed overhead would need to be 6. – Martin Smith Dec 15 '14 at 13:30
3

Identical tables, identical columns, identical records

Not entirely true. At first they were different, and row 1 was added to [mybit2] when they were different. Your second image, the DBCC PAGE output for [mybit2], clearly indicates this fact.

Why different record sizes?

Simply put: you changed the table definition and didn't rebuild it. Some operations can be carried out instantly (a.k.a. "online") as there is no pressing need to immediately reflect those changes on existing rows:

  • Adding a NULL field
  • Adding a NOT NULL, non-blob/UDT field with a default that is a runtime constant (starting in SQL Server 2012)
  • Dropping columns
  • Reclaiming space after adding the SPARSE option (sometimes) to a NULLable field (starting in SQL Server 2008)

    [ The MSDN page for ALTER TABLE has notes regarding: adding NOT NULL columns with a DEFAULT, dropping columns, and adding the SPARSE option. ]

In these cases, you need to rebuild the Clustered Index (or the table if it is a heap), which is when SQL Server will go through each record and make any necessary adjustments. Do the following to see it:

  1. drop the [mybit2] table
  2. run your script again to create [mybit2] with 8 fields, add a row, then ALTER to add the 9th field, and add a row
  3. exec: DBCC PAGE just to make sure that record 1 is 9 bytes and record 2 is 10 bytes
  4. now exec: ALTER TABLE dbo.mybit2 REBUILD;
  5. exec: DBCC IND(0, mybit2, -1); -- 0 = current DB since the rebuild likely moved the contents to another page
  6. exec: DBCC PAGE again (with the new PagePID!) and you will see that the "Record Size" for both records is now 10 bytes

Even more fun:

  1. exec: ALTER TABLE dbo.mybit2 DROP COLUMN col5;
  2. exec:

     DBCC TRACEON(3604) WITH NO_INFOMSGS;
     DBCC PAGE(0, 1, {PagePID}, 3) WITH TABLERESULTS; -- 0 = current DB
     DBCC TRACEOFF(3604) WITH NO_INFOMSGS;
    

    Both records still have a "Record Size" of 10 bytes, but what was col5 now shows as DROPPED.

  3. now exec: ALTER TABLE dbo.mybit2 REBUILD;
  4. exec: DBCC IND(0, mybit2, -1); -- 0 = current DB since the rebuild likely moved the contents to another page
  5. exec:

     DBCC TRACEON(3604) WITH NO_INFOMSGS;
     DBCC PAGE(0, 1, {newPagePID}, 3) WITH TABLERESULTS; -- 0 = current DB
     DBCC TRACEOFF(3604) WITH NO_INFOMSGS;
    

    Both records now have a "Record Size" of 9 bytes, and no more placeholder for col5 that shows as DROPPED in the Field column

2

Your second table has 8 columns, the first has 9. As the data type is "Bit", that accounts for exactly the difference.

  • How exactly did you calculate the difference? – Pavel Nefyodov Dec 12 '14 at 20:18
  • 8Bit + 1Bit (definition) For 8 Columns (of bit type), 9 Bit + 1Bit for 9. – Thomas Cleberg Dec 12 '14 at 20:26
  • That is not correct. In case of nine columns size would be 10 bytes = row overhead (4 bytes)+ NULL bitmap (4 bytes) + size of nine bit columns(2 bytes). – Pavel Nefyodov Dec 12 '14 at 20:47
  • Apologies, you are correct Reference though the addition of the 9th bit column to go from 1->2 byte storage still accounts for the difference. – Thomas Cleberg Dec 12 '14 at 20:53
0

Maybe I am missing something, but you realize that start off with 8 columns, do an insert then alter the table and add a 9th column and then do another insert. Of course the size is going to be different when the first insert was into 8 columns and the second was into 9 columns.

  • Could you please elaborate on "of course the size is going to be different"? – Pavel Nefyodov Dec 12 '14 at 20:25
  • You are going from 8 columns to 9 columns. 8 is not equal to 9, hence a size difference. Are you thinking it would not grow because you put a NULL in the last column? If so, it takes space even to store NULL value. A bit is a fixed-length data type so it allocates the space even when there is no value in anticipation of the future value. Just like a char[10] column is always 10 bytes long whether you put in 1 character, 10, or even a NULL. – Your_comment_is_not_funny Dec 12 '14 at 20:45
  • It seems like it is because column information is not stored in a record on new expanded table. – Pavel Nefyodov Dec 12 '14 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.