Filtered index not used when IS NULL and key lookup with no output
Filtered index not used when IS NULL is used. IS NOT NULL works fine, but IS NULL does a key lookup. Same as this issue but on different version of SQL Server
Upvotes: 1<=-=Jul 19 2017 11:13AM=-=>
Thanks for the feedback. I’ll (re) share the scenario with the engineering team.
Joe Sack, Principal PM, Microsoft
Daniel Smith commented
This had hundreds of votes on Connect before it was shut down. When will this be resolved?
Josh Ash commented
please fix this !
David Browne commented
Here's a repro (since the linked connect item is gone):
drop table if exists mytab
CREATE TABLE MyTab (
ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
,GroupByColumn NVARCHAR(10) NOT NULL
,WhereColumn int NULL
CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab
(GroupByColumn) --include (WhereColumn)
WHERE (WhereColumn IS NULL)
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn