Huge Tables: Minimizing Row Size - Remove Null Bitmap

CCDE

I am in the process of designing a SQL 2005 database with tables that may hold several hundreds of millions of rows.

Due to various constraints, I am trying to save as much space as possible by optimizing the size of a row. Currently one row contains the following columns:

byte(4), byte(3), byte(3) = 10 bytes.

Adding 4 bytes for the row header, plus 3 bytes for the null bitmap (as described in BOL) I am ending up with 17 bytes/row. In a real world test it was an average of 18.3 bytes/row.

There are no indexes, no primary key and all columns are not NULL. Hence my question: since no column allows a null value, is there a possibility to "remove" the 3 bytes Null Bitmap

Is there any other way to shave off one or two more bytes by using a clustered index etc

Thanks

Marcel van der Holst - MSFT


The nullbitmap only takes up 3 bits, not 3 bytes. For each column, a null bit will be added. There is no way to remove the null bitmap in SQL2005.

Could you post the schema you currenlty have, so that I could give some hints about how to optimize Are you storing data as binary() or varbinary

Thanks,

CCDE

Thanks a lot for looking into this.
Here is the table where I will be logging scientific data. I have already compressed each data element to save as many bytes as possible since the table is going to contain hundreds of millions of rows. TimeStamp is not unique, but it would be possible to define a primary key over all three columns.

Code Snippet

CREATE TABLE [dbo].[T_MCDGF_Series](
[TimeStampCompressed] [binary](4) NOT NULL,
[DECompressed] [binary](3) NOT NULL,
[ASWCompressed] [binary](3) NOT NULL
) ON [PRIMARY]

You are correct Marcel, the net NULL Bitmap size for 3 columns should be 3 BIT, but according to MSDN the minimum size of the NULL bitmap is 3 BYTE.
See here:
http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx
"Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )"
According to this description I have calculated the entire row size to be 17 bytes for the above table.

Finding a way to reduce the row size by just one byte would already be worth the trouble....

Chris

Marcel van der Holst - MSFT

Thanks for posting the CREATE TABLE SCRIPT.

The way SQL Server stores the information is as follows

- 2 bytes for record header
- 2 bytes to store the number of columns in the record
- 2 bytes for number of columns in the null bit map
- 1 bytes for null bit (because there are three columns)
- 10 bytes for the actual data

There is no way to get rid of the null bit map bytes

To find out the size that SQL Server is using, you could use the following:

CREATE TABLE [dbo].[T_MCDGF_Series](
[TimeStampCompressed] binary(4) NOT NULL,
[DECompressed] binary(3) NOT NULL,
[ASWCompressed] binary(3) NOT NULL
) ON [PRIMARY]

-- make sure you add at least one row
insert into [T_MCDGF_Series] values (1,2,3)

-- look at the avg_rec_size_in_bytes field for the size.
select * from sys.dm_db_index_physical_stats (db_id(), object_id('T_MCDGF_Series'), NULL, NULL, 'DETAILED')

Thanks,

Marcel.

CCDE

Interesting way to get the average row size.

I have tried to put all columns into a PK clustered index, which resulted in the same row size as the table without index. Bottom line, there is nothing more to optimize with regard to size. 70% storage overhead is a bit hefty in this case, but that number will go down signifficantly for tables with larger payload data size per row.

Tested with the above method, the smallest row size in SQL Server (storing just one byte of data) is 9 bytes.

Thanks.