Statistics for bit columns

-- Desired join order: D, C or E, B or F, A or G

/*
drop table Bits;
drop table BitLookup;
drop table Tinyints;
drop table TinyintLookup;
*/

----- Setup the Tables used in the Testing -----

set nocount on;

create table BitLookup (
    X bit not null primary key,
    V char(5) not null unique
);

create table TinyintLookup (
    X tinyint not null primary key,
    V char(5) not null unique
);

insert into BitLookup
output  inserted.X, inserted.V
into    TinyintLookup
values  (0, 'False');

insert into BitLookup
output  inserted.X, inserted.V
into    TinyintLookup
values  (1, 'True');

create table Bits (
    I int not null primary key,
    A bit not null references BitLookup(X),
    B bit not null references BitLookup(X),
    C bit not null references BitLookup(X),
    D bit not null references BitLookup(X),
    E bit not null references BitLookup(X),
    F bit not null references BitLookup(X),
    G bit not null references BitLookup(X),
    Z char(42) not null
);

create table Tinyints (
    I int not null primary key,
    A tinyint not null,
    B tinyint not null,
    C tinyint not null,
    D tinyint not null,
    E tinyint not null,
    F tinyint not null,
    G tinyint not null,
    Z char(42) not null
);

with g(n) as
(
    select  1
    union all
    select  n + 1
    from    g
    where   n < 1000000 -- 1 million rows
)
insert into Bits
output
    inserted.I, inserted.A, inserted.B,
    inserted.C, inserted.D, inserted.E,
    inserted.F, inserted.G, inserted.Z
into
    Tinyints
select
    n,
    n % 2,
    n % 5,
    n % 100,
    n % 10000, -- joining D first is best
    n % 100,
    n % 5,
    n % 2,
    replicate('.xX^Xx.', 6)
from
    g
option(maxrecursion 0);

alter table Tinyints add
foreign key (A) references TinyintLookup(X),
foreign key (B) references TinyintLookup(X),
foreign key (C) references TinyintLookup(X),
foreign key (D) references TinyintLookup(X),
foreign key (E) references TinyintLookup(X),
foreign key (F) references TinyintLookup(X),
foreign key (G) references TinyintLookup(X);

select @@version;

------ Execution Plan Testing Starts Here ------

declare @iteration int = 0;
-- 0 = cold state
-- 1 = warm state
while(@iteration <= 1)
begin
    if(@iteration = 0)
    begin
        print '
        --- COLD STATE ---';
        checkpoint;
        dbcc dropcleanbuffers with NO_INFOMSGS;
        dbcc freeproccache with NO_INFOMSGS;
    end
    else
        print '
        --- WARM STATE ---';

    print '
    Bits: Using lookup values
    ';
    set statistics profile on;
    set statistics io on;
    set statistics time on;

    select count(*)
    from Bits t
    join BitLookup a on a.X = t.A
    join BitLookup b on b.X = t.B
    join BitLookup c on c.X = t.C
    join BitLookup d on d.X = t.D
    join BitLookup e on e.X = t.E
    join BitLookup f on f.X = t.F
    join BitLookup g on g.X = t.G
    where
        a.V = 'False'
    and b.V = 'False'
    and c.V = 'False'
    and d.V = 'False'
    and e.V = 'False'
    and f.V = 'False'
    and g.V = 'False';

    set statistics time off;
    set statistics io off;
    set statistics profile off;

    print '
    Tinyints: Using lookup values
    ';
    set statistics profile on;
    set statistics io on;
    set statistics time on;

    select count(*)
    from Tinyints t
    join TinyintLookup a on a.X = t.A
    join TinyintLookup b on b.X = t.B
    join TinyintLookup c on c.X = t.C
    join TinyintLookup d on d.X = t.D
    join TinyintLookup e on e.X = t.E
    join TinyintLookup f on f.X = t.F
    join TinyintLookup g on g.X = t.G
    where
        a.V = 'False'
    and b.V = 'False'
    and c.V = 'False'
    and d.V = 'False'
    and e.V = 'False'
    and f.V = 'False'
    and g.V = 'False';

    set statistics time off;
    set statistics io off;
    set statistics profile off;

    if(@iteration = 0)
    begin
        checkpoint;
        dbcc dropcleanbuffers with NO_INFOMSGS;
        dbcc freeproccache with NO_INFOMSGS;
    end

    print '
    Bits: Using key values (preprocessing)
    ';
    declare @BitKey bit = (
        select X from BitLookup where V = 'False'
    );
    set statistics profile on;
    set statistics io on;
    set statistics time on;

    select count(*)
    from Bits t
    join BitLookup a on a.X = t.A
    join BitLookup b on b.X = t.B
    join BitLookup c on c.X = t.C
    join BitLookup d on d.X = t.D
    join BitLookup e on e.X = t.E
    join BitLookup f on f.X = t.F
    join BitLookup g on g.X = t.G
    where
        t.A = @BitKey
    and t.B = @BitKey
    and t.C = @BitKey
    and t.D = @BitKey
    and t.E = @BitKey
    and t.F = @BitKey
    and t.G = @BitKey;

    set statistics time off;
    set statistics io off;
    set statistics profile off;

    print '
    Tinyints: Using key values (preprocessing)
    ';
    declare @TinyintKey tinyint = (
        select X from TinyintLookup where V = 'False'
    );
    set statistics profile on;
    set statistics io on;
    set statistics time on;

    select count(*)
    from Tinyints t
    join TinyintLookup a on a.X = t.A
    join TinyintLookup b on b.X = t.B
    join TinyintLookup c on c.X = t.C
    join TinyintLookup d on d.X = t.D
    join TinyintLookup e on e.X = t.E
    join TinyintLookup f on f.X = t.F
    join TinyintLookup g on g.X = t.G
    where
        t.A = @TinyintKey
    and t.B = @TinyintKey
    and t.C = @TinyintKey
    and t.D = @TinyintKey
    and t.E = @TinyintKey
    and t.F = @TinyintKey
    and t.G = @TinyintKey;

    set statistics time off;
    set statistics io off;
    set statistics profile off;

    set @iteration = @iteration + 1;
end

-- stats have been automatically generated for the bit columns
dbcc SHOW_STATISTICS ('Bits', 'A') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Bits', 'B') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Bits', 'C') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Bits', 'D') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Bits', 'E') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Bits', 'F') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Bits', 'G') with NO_INFOMSGS;

-- stats have been automatically generated for the tinyint columns
dbcc SHOW_STATISTICS ('Tinyints', 'A') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Tinyints', 'B') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Tinyints', 'C') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Tinyints', 'D') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Tinyints', 'E') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Tinyints', 'F') with NO_INFOMSGS;
dbcc SHOW_STATISTICS ('Tinyints', 'G') with NO_INFOMSGS;