indexing - Filtered Index in SQL Server missing predicate does not work as expected -

i experimenting filtered indexes in sql server. trying shrink filtered index down putting following hint bol practice:

a column in filtered index expression not need key or included column in filtered index definition if filtered index expression equivalent query predicate , query not return column in filtered index expression query results.

i have reproduced problem in small test script: table looks follows:

create table #test (     id  bigint not null identity(1,1),     archivedate datetime null,     closingdate datetime null,     objecttype integer not null,     active bit not null,     filler1 char(255) default 'just filler',     filler2 char(255) default 'just filler',     filler3 char(255) default 'just filler',     filler4 char(255) default 'just filler',     filler5 char(255) default 'just filler',     constraint test_pk primary key clustered (id asc) ); 

i need optimize following query:

select       count(*)          #test                 archivedate null      , closingdate not null      , isnull(active,1) != 0 

therefore have built following filtered index:

create nonclustered index idx_filtertest on #test (/*archivedate asc,*/closingdate asc) include (active) archivedate null; 

archivedate in filter , not used in select it's not contained in index keys or includes.

however, if run query following plan:

there's key lookup in clustered index archivedate. why so? have reproduced behaviour on sql server 2008 , sql server 2016.

if create index archivedate in key away index seek. seems me paragraph in bol doesn't apply.

here's complete repro script:

--drop table #test; create table #test (     id  bigint not null identity(1,1),     archivedate datetime null,     closingdate datetime null,     objecttype integer not null,     active bit not null,     filler1 char(255) default 'just filler',     filler2 char(255) default 'just filler',     filler3 char(255) default 'just filler',     filler4 char(255) default 'just filler',     filler5 char(255) default 'just filler',     constraint test_pk primary key clustered (id asc) );    insert #test (archivedate, closingdate, objecttype, active) select top 200     null,     dates.calcdate,     4711,     dates.number%2     (         select             /* erzeugen des datums durch addieren der jeweiligen sequenznummer zum startdate */             dateadd(day, seq.number, '20120101') calcdate, number                 (             /* abfrage zur erstellung einer nummernsequenz von 0 bis 9999. dient als basis zur aufbereitung aller datumswerte im zeitraum. die sequenz reicht für einen zeitraum von ca. 30 jahren aus. */             select                 a.num * 1000 + b.num * 100 + c.num * 10 + d.num number                                     ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) c             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) d         ) seq                      /* einschränkung der nummernsequenz auf die anzahl der tage im gewünschten aufbereitungszeitraum */             seq.number <= 5000     ) dates order     dates.number ;    insert #test (archivedate, closingdate, objecttype, active) select top 1000     dates.calcdate + 3,     dates.calcdate,     4711,     dates.number%2     (         select             /* erzeugen des datums durch addieren der jeweiligen sequenznummer zum startdate */             dateadd(day, seq.number, '20120101') calcdate, number                 (             /* abfrage zur erstellung einer nummernsequenz von 0 bis 9999. dient als basis zur aufbereitung aller datumswerte im zeitraum. die sequenz reicht für einen zeitraum von ca. 30 jahren aus. */             select                 a.num * 1000 + b.num * 100 + c.num * 10 + d.num number                                     ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) c             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) d         ) seq                      /* einschränkung der nummernsequenz auf die anzahl der tage im gewünschten aufbereitungszeitraum */             seq.number <= 5000     ) dates order     dates.number ;   insert #test (archivedate, closingdate, objecttype, active) select top 100000     dates.calcdate,     null,     4711,     dates.number%2     (         select             /* erzeugen des datums durch addieren der jeweiligen sequenznummer zum startdate */             dateadd(day, seq.number, '20120101') calcdate, number                 (             /* abfrage zur erstellung einer nummernsequenz von 0 bis 9999. dient als basis zur aufbereitung aller datumswerte im zeitraum. die sequenz reicht für einen zeitraum von ca. 30 jahren aus. */             select                 a.num * 1000 + b.num * 100 + c.num * 10 + d.num number                                     ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) c             cross join  ( select 0 num union select 1 num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) d         ) seq                      /* einschränkung der nummernsequenz auf die anzahl der tage im gewünschten aufbereitungszeitraum */             seq.number <= 5000     ) dates order     dates.number ;   --drop index idx_filtertest on #test; --create nonclustered index idx_filtertest on #test (archivedate asc,closingdate asc) include (active) archivedate null; create nonclustered index idx_filtertest on #test (/*archivedate asc,*/closingdate asc) include (active) archivedate null;    select       count(*)          #test                 archivedate null      , closingdate not null      , isnull(active,1) != 0; 

this bug in optimizer, in way handles is null filters. here's simpler repro:

create table #t(id int identity primary key, x int); insert #t(x) select top(10000) message_id sys.messages message_id <> 1; insert #t(x) values (1); insert #t(x) values (null); create index ix_#t_x_null on #t(id) x null; create index ix_#t_x_1 on #t(id) x = 1; 

clearly following query covered ix_#t_x_null:

select min(id) #t x null; 

and optimizer indeed picks it, execution plan superfluous clustered index lookup inserted. but:

select min(id) #t x = 1; 

now query without clustered index lookup. when is null involved, optimizer seems recognize filtered index applies, unable propagate condition later step. can see if include column index:

create index ix_#t_x_null on #t(id, x) x null; 

if compare execution plans of where x = 1 , where x null queries, you'll see in case of x null, optimizer adds predicate index scan, doesn't x = 1.

and delving bit bit further, specific setup, can find known issue, reported on connect. according microsoft, however, "this not bug rather known gap in functionality" (which suppose technically true, since results not incorrect, doesn't perform could). also, "this active dcr future release of sql server", 6 years ago, , ticket closed "won't fix" -- don't hold breath.

unfortunately, workaround indeed include column in index -- i'd make included column , not key, since adds overhead non-leaf levels:

create nonclustered index idx_filtertest on #test (closingdate asc) include (active, archivedate)  archivedate null; 

i "unfortunately" because always-null column still pointlessly take row space (since datetime fixed size data type). so, it's miles better getting i/o clustered index lookups. also, overhead can reduced nothing compressing index (even row compression do).