SQL Server packing multiple bit fields in a table

Question

I'm using SQL Server 2016, but I think the question should apply to all versions of SQL Server ...

I understand that if you have multiple bit fields in a table that the fields are packed together to save space. Will SQL Server pack the bit fields even if they don't appear contiguously, like this?:-

create table test (
    field1 int,
    field2 bit,
    field3 varchar(100),
    field4 bit,
    field5 varchar(100),
    field6 bit
)

Or do I need to put all the bit fields together, like this:-

create table test (
    field1 int,
    field3 varchar(100),
    field5 varchar(100),
    field2 bit,
    field4 bit,
    field6 bit
)

Answer 1

TL;DR; SQL Server will pack bit fields no matter the order of their definition.

If we were to run the following

create table test_dispersed (
    field1 int,
    field2 bit,
    field3 varchar(100),
    field4 bit,
    field5 varchar(100),
    field6 bit
)

create table test_contiguous (
    field1 int,
    field3 varchar(100),
    field5 varchar(100),
    field2 bit,
    field4 bit,
    field6 bit
)

insert into test_dispersed (field1, field2, field3, field4, field5, field6) values (123,0,'A string',1,'Another string',0);
insert into test_contiguous (field1, field2, field3, field4, field5, field6) values (123,0,'A string',1,'Another string',0);


SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDbName'), OBJECT_ID(N'[dbo].[test_dispersed]'), NULL, NULL , 'DETAILED')
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDbName'), OBJECT_ID(N'[dbo].[test_contiguous]'), NULL, NULL , 'DETAILED')

You will see for both tables the [max_record_size_in_bytes, min_record_size_in_bytes, avg_record_size_in_bytes] are all the same and equal across tables at 54 bytes.