SQLTeam.com | Weblogs | Forums

How "bit" data types are grouped together in db design


#1

SQL Server is able to group "bit" columns together into bytes to save row space. But I'm wondering if it is necessary to have all the bit columns next to each other in the table design in order for that to happen? In other words, does it matter if bit columns are separated by columns of other types, or does SQL Server always group bit columns together to save space no matter where they are in the table design?


#2

I like to group my BIT columns together, but it makes no different to SQL Server - it will minimise the number of bytes used to store BIT column data regardless of the physical design of the table.

It will save bits & bytes if the BIT columns are declared as NOT NULL ...


#3

I can confirm that -- SQL rearranges all the columns you put in the table as it needs to, including bit columns -- and all other column types as well.

I believe SQL now uses a NULL bit flag for all columns, even if you specify "NOT NULL" for that column.


#4

Thank you, that's very helpful!


#5

Didn't know that, useful info thanks.

Probably much easier to have the same bit pattern for both NULL and NOT NULL columns ... although having cut my teeth in the days of 4K RAM boards !! the wastage offends me ...